PostgreSQL 10升级报错：CASE中不允许使用集合返回函数

问题原因 #

PostgreSQL 10及后续版本严格规范了集合返回函数（比如jsonb_array_elements_text）的使用场景，禁止在CASE表达式这类期望返回单一值的结构中调用它们。而PostgreSQL 9.6的宽松处理允许这种非标准用法，会隐式将函数返回的多个元素拆分为单独行，升级后这种行为被修复，因此触发报错。

修复方案 #

方案1：使用LATERAL JOIN（推荐） #

这种方式能准确复现9.6版本的输出结果，同时符合新版本的语法规范：

SELECT 
    COALESCE(concat('RR', elem), '') AS concat
FROM workflow.workflow_log_history wlh
LEFT JOIN LATERAL (
    -- 仅处理匹配%DR%的行，展开JSON数组元素
    SELECT jsonb_array_elements_text(replace(wlh.status_reason_code, 'DR', '')::jsonb) AS elem
    WHERE wlh.status_reason_code LIKE '%DR%'
) sub ON true;

• LEFT JOIN LATERAL确保所有行都能被返回，不管是否匹配%DR%
• 匹配的行通过子查询展开数组元素，每一个元素生成一行
• COALESCE将不匹配行的NULL替换为空字符串，和原逻辑一致

方案2：用UNION ALL分情况处理 #

如果更习惯分条件写逻辑，可以用UNION ALL拆分两种场景：

-- 处理匹配%DR%的行，展开数组并拼接前缀
SELECT concat('RR', jsonb_array_elements_text(replace(wlh.status_reason_code, 'DR', '')::jsonb)) AS concat
FROM workflow.workflow_log_history wlh
WHERE wlh.status_reason_code LIKE '%DR%'

UNION ALL

-- 处理不匹配的行，返回空字符串
SELECT '' AS concat
FROM workflow.workflow_log_history wlh
WHERE wlh.status_reason_code NOT LIKE '%DR%';

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