如何在PostgreSQL函数中返回新的聚合结果集？

错误原因 #

你声明函数返回records（原数据表的行类型），但查询实际返回的是sex、city、count三个字段的聚合结果，结构与原表完全不匹配，因此触发return type mismatch错误。

解决方案 #

方案1：直接指定返回表结构（最简便） #

使用RETURNS TABLE()明确定义函数返回的列名和数据类型，无需额外创建类型：

CREATE FUNCTION generate_report_trial()
RETURNS TABLE(sex text, city text, count bigint) AS $$
    SELECT 
        form #>> '{sex}' AS sex,
        form #>> '{city}' AS city,
        count(*) AS count
    FROM records 
    GROUP BY form #>> '{sex}', form #>> '{city}';
$$ LANGUAGE SQL STABLE;

调用函数：

SELECT * FROM generate_report_trial();

执行后会得到预期的聚合结果：

方案2：先定义复合类型再返回集合 #

如果需要在多个函数中复用该返回结构，可以先创建自定义复合类型：

CREATE TYPE report_result AS (
    sex text,
    city text,
    count bigint
);

然后让函数返回该类型的集合：

CREATE FUNCTION generate_report_trial()
RETURNS SETOF report_result AS $$
    SELECT 
        form #>> '{sex}',
        form #>> '{city}',
        count(*)
    FROM records 
    GROUP BY form #>> '{sex}', form #>> '{city}';
$$ LANGUAGE SQL STABLE;

调用方式与方案1一致。

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