Postgres中UNION操作时如何引用子查询结果作为自定义函数入参

错误根因 #

FROM子句中定义的子查询别名subquery的作用域仅覆盖它所属的SELECT查询块，UNION 语句两侧的查询是相互独立的执行单元，后半段调用link_traversal函数的查询无法访问到前半段定义的别名，因此会抛出relation "subquery" does not exist报错。

解决方案 #

使用WITH子句定义公用表表达式（CTE）即可实现子查询结果的复用，WITH中定义的别名作用域覆盖整个后续查询，既能满足把most_recent_sit的执行结果作为入参传给link_traversal的需求，也能正常完成UNION合并逻辑。
修正后的SQL如下：

WITH subquery AS (
    SELECT parent_id, (most_recent_sit(date, child_id, key)).*
    FROM links
    WHERE child_id = ANY(source_ids)
    GROUP BY parent_id
)
SELECT parent_id FROM subquery
UNION
SELECT * FROM link_traversal(
    ARRAY(SELECT parent_id FROM subquery)
);

注：原语句中link_traversal的入参末尾多了多余的逗号，上述代码已做删除处理。
如果你的PostgreSQL版本支持且subquery返回的数据量较大，可以给CTE添加MATERIALIZED关键字强制提前物化结果，避免重复计算。

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