PostgreSQL中能否将多行多列SELECT结果作为函数参数？如何避免重复编写聚合语句？

原预期写法的可行性判断 #

直接将多行多列SELECT用括号包裹作为函数参数的写法不可行，核心限制为：

• 普通括号包裹的子查询在PostgreSQL中属于标量子查询，语法要求最多返回单行单列，返回多行时会直接抛出“子查询返回多行被用作表达式”的错误。
• 常规自定义函数参数不支持直接接收无固定结构的多行结果集，无法自动匹配传入查询的列类型。

可落地的简化实现 #

可以利用PostgreSQL的多态类型机制编写通用转换函数，从根本上避免反复编写COALESCE(jsonb_agg(tmp)::text, '[]') FROM (...) AS tmp的重复代码，写法接近你预期的简洁形式。

创建通用转换函数 #

根据返回类型需求分别创建对应函数：

• 匹配需求的text返回版本：

CREATE OR REPLACE FUNCTION my_to_json_string(input_set anyelement)
RETURNS text
LANGUAGE sql IMMUTABLE PARALLEL SAFE
AS $$
  SELECT COALESCE(jsonb_agg(input_set)::text, '[]') FROM input_set
$$;

• 可选的原生jsonb返回版本：

CREATE OR REPLACE FUNCTION my_to_jsonb(input_set anyelement)
RETURNS jsonb
LANGUAGE sql IMMUTABLE PARALLEL SAFE
AS $$
  SELECT COALESCE(jsonb_agg(input_set), '[]'::jsonb) FROM input_set
$$;

调用方式 #

只需要将任意多行多列的SELECT查询，用PostgreSQL内置的TABLE()语法包裹后作为参数传入即可：

SELECT my_to_json_string(TABLE(SELECT id, balance FROM student LIMIT 5));

该调用的输出完全匹配预期：

[{"id": 21543, "balance": 80}, {"id": 21542, "balance": 100}, {"id": 21541, "balance": 5980}, {"id": 21540, "balance": 10}, {"id": 21539, "balance": 15}]

当查询结果为空时，函数会自动返回'[]'，不会输出null值，和手动编写的COALESCE逻辑效果完全一致。

补充说明 #

• 该函数为纯SQL实现，和原生手写的jsonb_agg逻辑性能无差异，无额外运行时开销，支持并行查询和查询结果缓存。
• 必须使用TABLE()包裹传入的行集，省略后会触发多行标量子查询的语法错误。

内容的提问来源于stack exchange，提问作者Soul Clinic