PostgreSQL报set-returning functions不允许在WHERE使用如何解决

unnest()是PostgreSQL中的集合返回函数（Set-Returning Function，简称SRF），这类函数执行后会返回0到N行结果集。PostgreSQL语法不允许在WHERE子句中直接使用集合返回函数：WHERE子句的逻辑是对每一行输入做单次布尔值判断，无法直接处理可能返回多行的集合结果，因此抛出set-returning functions are not allowed in WHERE的错误。

写法1：CTE/子查询预处理解析结果 #

先通过公共表表达式提前完成XML节点的解析和unnest展开，再对解析后的字段做条件过滤，逻辑最直观易读：

WITH parsed_msg AS (
    SELECT
        unnest(xpath(
            '//ns2:ProcedureCategory/text()',
            messagebody::xml,
            array[array['ns2','urn:wco:datamodel:WCO:DEC-DMS:2']]
        ))::text AS procedure_category
    FROM sw_customs_message scm
)
SELECT procedure_category
FROM parsed_msg
WHERE procedure_category = 'H7';

写法2：使用xpath_exists做前置过滤（性能更优） #

PostgreSQL原生提供xpath_exists函数用来判断XML中是否存在匹配指定规则的节点，不需要执行unnest操作即可完成条件判断，数据量大时执行效率更高：

SELECT
    unnest(xpath(
        '//ns2:ProcedureCategory/text()',
        messagebody::xml,
        array[array['ns2','urn:wco:datamodel:WCO:DEC-DMS:2']]
    ))::text AS procedure_category
FROM sw_customs_message scm
WHERE xpath_exists(
    '//ns2:ProcedureCategory[text()="H7"]',
    messagebody::xml,
    array[array['ns2','urn:wco:datamodel:WCO:DEC-DMS:2']]
);

注意：如果单条messagebody中存在多个ns2:ProcedureCategory节点，只要其中任意一个节点值为'H7'，该记录就会被匹配返回，和最初的预期筛选逻辑一致。

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