如何将多个返回表的PostgreSQL函数合并为返回JSON的函数

问题原因 #

你遇到的错误是因为UNION操作需要对结果行进行去重，而PostgreSQL的json类型没有内置的相等比较运算符，无法判断两个json值是否相等，因此无法完成去重逻辑，导致报错。

解决方案 #

方案1：先合并原始数据，再转换为JSON（推荐） #

先通过UNION合并所有子函数的结构化结果（这些字段如date、text都有默认的相等运算符），完成去重后再逐行转换为JSON：

SELECT row_to_json(t)
FROM (
    SELECT "data type", "A date", "some text", "Another text field"
    FROM db.subfunction1()
    UNION
    SELECT "data type", "A date", "some text", "Another text field"
    FROM db.subfunction2()
    UNION
    SELECT "data type", "A date", "some text", "Another text field"
    FROM db.subfunction3()
) AS t;

如果不需要去重，把UNION换成UNION ALL可以提升性能。

方案2：用UNION ALL替代UNION #

如果你的场景不需要去重，直接使用UNION ALL即可避免去重时的json比较操作：

SELECT row_to_json(t) 
FROM (SELECT "data type", "A date", "some text", "Another text field" FROM db.subfunction1()) t
UNION ALL
SELECT row_to_json(t) 
FROM (SELECT "data type", "A date", "some text", "Another text field" FROM db.subfunction2()) t
UNION ALL
SELECT row_to_json(t) 
FROM (SELECT "data type", "A date", "some text", "Another text field" FROM db.subfunction3()) t;

方案3：转换为jsonb类型 #

jsonb类型是PostgreSQL中支持相等比较的二进制JSON格式，你可以用to_jsonb替换row_to_json，这样UNION可以正常工作：

SELECT to_jsonb(t)
FROM (SELECT "data type", "A date", "some text", "Another text field" FROM db.subfunction1()) t
UNION
SELECT to_jsonb(t)
FROM (SELECT "data type", "A date", "some text", "Another text field" FROM db.subfunction2()) t
UNION
SELECT to_jsonb(t)
FROM (SELECT "data type", "A date", "some text", "Another text field" FROM db.subfunction3()) t;

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