PostgreSQL如何从函数中返回JSON？

Question

我了解到可以使用row_to_json来返回json格式的输出。

例如，如果我的查询是：

select * from sample;

我可以将其重写为以下形式，以返回json输出：

select row_to_json(sample) from sample;

但我想要实现的一件事情是在函数中实现相同的功能。

为了给你举个例子，这里有一个返回表格的函数：

CREATE FUNCTION find_val(val text) 
RETURNS SETOF sample AS
$$
BEGIN
RETURN QUERY
SELECT * FROM sample where $1 = ANY(col4);
END;
$$
LANGUAGE 'plpgsql';

现在我想从我的函数返回 JSON 输出，而不是行。我该怎么做呢？

到目前为止，我尝试了以下方法：

native=> CREATE FUNCTION find_val(val text) 
RETURNS SETOF sample AS
$$
BEGIN
RETURN QUERY
SELECT row_to_json(sample) FROM sample where $1 = 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 $1 = 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=> 

- Mandeep Singh

Answer 1

这与使用JSON还是其他返回类型无关。在PL/PgSQL函数中，您不能使用简单的SELECT，必须使用SELECT INTO、RETURN QUERY SELECT或PERFORM，根据HINT错误提示。

在您的情况下，您只需要一个普通的SQL函数。

CREATE FUNCTION find_val(val text) 
RETURNS json AS
$$
SELECT row_to_json(sample) FROM sample where $1 = ANY(col4);
$$ LANGUAGE sql;

- Craig Ringer

---

谢谢 Craig，这个可行。我需要在我的实际函数中执行更多操作，所以只使用了 plpgsql。从官方文档中找到了正确的方法。创建或替换函数 find_val(val text) 返回 json AS $$ DECLARE t_row sample%ROWTYPE; BEGIN SELECT * INTO t_row FROM sample where $1 = ANY(col4); RETURN row_to_json(t_row); END; $$ 语言 'plpgsql'; - Mandeep Singh