将函数的SELECT结果作为JSON(Postgres)返回

Question

我有一个plpgsql函数和一个复杂的嵌套SELECT查询，我想将查询的结果表作为JSON返回。我该怎么做？我有这样的东西：

CREATE TEMPORARY TABLE selectRESULTS AS
 /*some long, complicated, and nested SELECT query
 .............
 ..............
 */
RETURN array_to_json(array_agg(selectResults));

---

编辑：我在尝试了的一个解决方案后添加了额外的规范

使用Pozs方法：

RETURN (SELECT json_agg(selectResults) FROM selectResults);

我确实得到了一个json。然而，它嵌套了额外的数组，这使得内部对象更难访问。

目前我得到

[['[{"someKey": someValue}, etc]']]

有没有办法去掉两个外部阵列，这样就不用了

somejson[0][0][0]["someKey"]访问someValue，我可以使用someJson[0]["someKey"]吗？

提前感谢！

Answer 1

简而言之，您的语法有一个错误；您需要一个SELECT来使用聚合函数，如array_agg。您也可以使用array()构造函数：

-- do NOT use these, these are far from optimal
RETURN array_to_json(array(SELECT selectResults FROM selectResults));
-- or
RETURN (SELECT array_to_json(array_agg(selectResults)) FROM selectResults);

但json_agg(...)的效果与array_to_json(array_agg(...))相同，但更快：

RETURN (SELECT json_agg(selectResults) FROM selectResults);

注意：我使用selectResults来选择selectResults表的整行，但如果其中只有1列，则可以直接使用。

但是，您可能根本不需要临时表。如果你的逻辑很简单，你可以使用一个简单的sql函数，比如：

create function xyz(p1_type, ..., pn_type)
  returns json
  language sql
as $func$
  select json_agg(select_results)
  from (
    /*some SELECT query here
     .............
     use $1 ... $<n> for parameters here,
     or you can name them also within sql functions
     .............
     */
  ) select_results
$func$;