PostgreSQL中string_agg函数的数据类型匹配问题

你遇到的报错Returned type text does not match expected type character varying in column 2，本质是PostgreSQL的类型校验规则导致的：

• string_agg()函数的返回类型固定为text（无论输入是varchar还是text，内部都会统一按text处理并返回）
• 表值函数的RETURNS TABLE定义了严格的列类型，PostgreSQL不会自动对返回结果做隐式类型转换，因此text类型的返回值无法匹配varchar类型的列定义。

不需要每次都用显式强制转换，以下两种方法更简洁：

1. 统一返回类型为text #

直接修改函数的返回列类型为text，完全匹配string_agg()的输出，这是最省心的方案：

CREATE FUNCTION doit(selectedcategory varchar)
RETURNS TABLE(cat varchar, items text)
LANGUAGE PLPGSQL
AS $$ BEGIN
    RETURN QUERY
    SELECT category, string_agg(data,'|')
    FROM test
    WHERE category=selectedcategory
    GROUP BY category;
END $$;

PostgreSQL中text和无长度限制的varchar功能完全等价，这种修改不会影响业务逻辑，还能避免转换开销。

2. 让string_agg返回varchar类型 #

如果业务必须保留varchar的返回类型，可以通过转换string_agg()的输入参数，让它返回varchar：

CREATE FUNCTION doit(selectedcategory varchar)
RETURNS TABLE(cat varchar, items varchar)
LANGUAGE PLPGSQL
AS $$ BEGIN
    RETURN QUERY
    SELECT category, string_agg(data::varchar, '|')
    FROM test
    WHERE category=selectedcategory
    GROUP BY category;
END $$;

当输入参数为varchar时，string_agg()会返回对应类型的结果，完美匹配函数的返回定义。

你提到CTE是冗余结构，直接去掉后代码会更简洁，性能也不会有影响（PostgreSQL会自动优化CTE的执行计划）。

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