如何在PostgreSQL中编写SQL Server迁移的递归CTE查询

核心语法适配点 #

• 递归CTE声明：PostgreSQL 要求递归公用表表达式必须显式添加RECURSIVE关键字，基础递归逻辑（锚点成员+UNION ALL递归成员）和SQL Server写法完全兼容。
• 层级路径拼接：SQL Server 中STUFF() + FOR XML PATH的有序字符串拼接逻辑，可直接用PostgreSQL 内置的string_agg()有序聚合函数替代，无需手动截取首字符，也不存在XML转义特殊字符的风险。
• 临时表创建：SQL Server 中SELECT INTO #临时表的写法，对应PostgreSQL 的CREATE TEMPORARY TABLE ... AS语法，临时表为会话级，会话结束自动清理，无需#前缀标识。
• 字段引用规范：PostgreSQL 对多表关联场景下的字段归属校验更严格，关联查询中的过滤字段必须明确指定表别名，避免出现字段引用歧义报错。

适配后可直接运行的PostgreSQL代码 #

WITH RECURSIVE temp AS (
    -- 锚点：查询所有有效状态的分配节点作为递归起点
    SELECT
        a.AssignNo AS LowestAssignNo,
        a.AssignNo,
        a.AssignName,
        a.HierarchyLevel,
        a.UpperAssignNo
    FROM TAssignInfo a
    WHERE a.DeleteYesNo = 'N'
      AND a.UseYesNo = 'Y'

    UNION ALL

    -- 递归向上关联上级分配节点
    SELECT
        a.LowestAssignNo,
        b.AssignNo,
        b.AssignName,
        b.HierarchyLevel,
        b.UpperAssignNo
    FROM temp a
    INNER JOIN TAssignInfo b
        ON a.UpperAssignNo = b.AssignNo
    WHERE b.DeleteYesNo = 'N'
      AND b.UseYesNo = 'Y'
)
-- 结果写入临时表
CREATE TEMPORARY TABLE NewAssignInfo AS
SELECT DISTINCT
    b.LowestAssignNo,
    string_agg(
        a.AssignName,
        '|' ORDER BY a.HierarchyLevel
    ) FILTER (WHERE a.HierarchyLevel > 1) AS AssignNamePath
FROM temp b
LEFT JOIN temp a
    ON a.LowestAssignNo = b.LowestAssignNo
WHERE b.HierarchyLevel <> 0
GROUP BY b.LowestAssignNo
ORDER BY b.LowestAssignNo DESC;

逻辑一致性说明 #

• 完全保留原有递归逻辑：从所有有效分配节点出发，向上递归追溯全部上级关联节点，和原SQL Server递归返回的数据集完全一致。
• 路径拼接规则完全对齐：仅拼接层级大于1的节点名称，按HierarchyLevel升序排列，用|分隔，和原STUFF拼接结果无差异。
• 结果过滤、排序规则和原逻辑一致：排除HierarchyLevel=0的节点，按LowestAssignNo倒序排列，结果去重后写入临时表。

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