postgresql postgres 从函数返回 json
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24220409/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgres return json from a function
提问by Mandeep Singh
I got to know that I can use row_to_json to return json output
我知道我可以使用 row_to_json 来返回 json 输出
For example If my query is:
例如，如果我的查询是：
select * from sample;
I can rewrite it as follows to return json output:
我可以按如下方式重写它以返回 json 输出：
select row_to_json(sample) from sample;
But one thing I am trying to achieve is the same functionality in the function.
但我试图实现的一件事是函数中的相同功能。
To give you an example, here is the function returning table:
举个例子，这里是函数返回表：
CREATE FUNCTION find_val(val text)
RETURNS SETOF sample AS
$$
BEGIN
RETURN QUERY
SELECT * FROM sample where = ANY(col4);
END;
$$
LANGUAGE 'plpgsql';
Now instead of rows, I want to return JSON output from my function. How can I do that ?
现在，我想从我的函数返回 JSON 输出，而不是行。我怎样才能做到这一点 ？
Here is what I have tried so far:
这是我迄今为止尝试过的：
native=> CREATE FUNCTION find_val(val text)
RETURNS SETOF sample AS
$$
BEGIN
RETURN QUERY
SELECT row_to_json(sample) FROM sample where = ANY(col4) ;
END;
$$
LANGUAGE 'plpgsql';
CREATE FUNCTION
native=> select find_val('yo');
ERROR: structure of query does not match function result type
DETAIL: Returned type json does not match expected type integer in column 1.
CONTEXT: PL/pgSQL function find_val(text) line 3 at RETURN QUERY
native=> drop function find_val(text);
DROP FUNCTION
native=> CREATE FUNCTION find_val(val text)
native-> RETURNS json AS
native-> $$
native$> BEGIN
native$> SELECT row_to_json(sample) FROM sample where = ANY(col4);
native$> END;
native$> $$
native-> LANGUAGE 'plpgsql';
CREATE FUNCTION
native=> select find_val('yo');
ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function find_val(text) line 3 at SQL statement
native=>
回答by Craig Ringer
This is nothing to do with json vs other return types. You can't use plain SELECTin a PL/PgSQL function, it has to be SELECT INTO, RETURN QUERY SELECT, or PERFORM. Per the HINTerror.
这与 json 与其他返回类型无关。不能使用普通SELECT的PL / pgSQL函数，它必须是SELECT INTO，RETURN QUERY SELECT或PERFORM。根据HINT错误。
In your case all you need is a plain SQL function.
在您的情况下，您只需要一个普通的 SQL 函数。
CREATE FUNCTION find_val(val text)
RETURNS json AS
$$
SELECT row_to_json(sample) FROM sample where = ANY(col4);
$$ LANGUAGE sql;
