将含多递归成员的SQL Server CTE转换为PostgreSQL的报错解决

问题背景 #

原SQL是Itzik Ben-Gan于2013年发表在ITProToday的SQL Server递归CTE，用于从家族树表中获取指定成员的所有祖先数据。在PostgreSQL中运行时会抛出错误：recursive reference to query "c" must not appear within its non-recursive term，而在SQL Server中移除recursive和Temporary关键字后可正常返回8行结果。

报错原因 #

PostgreSQL对递归CTE的语法规范要求更严格：递归CTE必须明确划分为非递归锚点段和递归段，且两段之间只能用一个UNION ALL分隔。原SQL的锚点段使用了两个UNION ALL拆分父亲、母亲的查询，导致PostgreSQL无法正确识别递归结构，误判递归引用位置。

修改后的SQL代码 #

DROP TABLE IF EXISTS tmp_FamilyTree;
CREATE temporary TABLE tmp_FamilyTree
(
    id INT NOT NULL CONSTRAINT PK_FamilyTree PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    mother INT NULL,
    father INT NULL
);
INSERT INTO tmp_FamilyTree
(
    id,
    name,
    mother,
    father
)
VALUES
(   1,
    'Balbo Baggins',
    NULL,
    NULL),
(   2,
    'Berylla Boffin',
    NULL,
    NULL),
(   3,
    'Mungo Baggins',
    1,
    2),
(   4,
    'Laura Grubb',
    NULL,
    NULL),
(   5,
    'Bungo Baggins',
    3,
    4),
(   6,
    'Belladonna Took',
    NULL,
    NULL),
(   7,
    'Bilbo Baggins',
    5,
    6),
(   8,
    'Largo Baggins',
    1,
    2),
(   9,
    'Tanta Hornblower',
    NULL,
    NULL),
(   10,
    'Fosco Baggins',
    8,
    9),
(   11,
    'Ruby Bolger',
    NULL,
    NULL),
(   12,
    'Dora Baggins',
    10,
    11),
(   13,
    'Drogo Baggins',
    10,
    11),
(   14,
    'Dudo Baggins',
    10,
    11),
(   15,
    'Primula Brandybuck',
    NULL,
    NULL),
(   16,
    'Frodo Baggins',
    13,
    15);

WITH recursive C
AS (
    -- 合并后的锚点查询：一次性获取目标成员的父母
    SELECT parent_id AS id, 1 AS lvl
    FROM (
        SELECT father AS parent_id FROM tmp_FamilyTree WHERE id = 16 AND father IS NOT NULL
        UNION ALL
        SELECT mother AS parent_id FROM tmp_FamilyTree WHERE id = 16 AND mother IS NOT NULL
    ) AS parents
    UNION ALL
    -- 递归查询：获取上一级成员的父母
    SELECT P.father AS id, C.lvl + 1 AS lvl
    FROM C
    JOIN tmp_FamilyTree AS P ON C.id = P.id
    WHERE P.father IS NOT NULL
    UNION ALL
    SELECT P.mother AS id, C.lvl + 1 AS lvl
    FROM C
    JOIN tmp_FamilyTree AS P ON C.id = P.id
    WHERE P.mother IS NOT NULL
)
SELECT C.id, FT.name, lvl
FROM C
JOIN tmp_FamilyTree AS FT ON C.id = FT.id;

修改说明 #

• 将原锚点中拆分的父亲、母亲查询合并到一个子查询中，确保递归CTE的非递归锚点段是一个独立的整体。
• 保留原递归逻辑不变，仅调整结构以符合PostgreSQL对递归CTE的语法要求。
• 修改后在PostgreSQL中运行可返回与SQL Server一致的8行祖先数据结果。

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