从Oracle迁移至PostgreSQL：带链接的递归SQL查询实现

我有一张名为NodeHierarchy的表，结构及数据如下：

在Oracle中，我可以通过以下查询获取包含链接追踪的所需结果：

select distinct pe.*
from (SELECT p.id
      FROM NodeHierarchy p
               JOIN (SELECT pi.PARENTID, pi.ID
                     FROM NodeHierarchy pi
                     UNION ALL
                     SELECT d.PARENTID, s.ID
                     FROM NodeHierarchy s
                              JOIN NodeHierarchy d ON d.ID = s.PARENTID) children ON children.ID = p.ID
      START WITH children.PARENTID = 6
      CONNECT BY NOCYCLE PRIOR p.linkedTargetId = children.PARENTID) tree,
     NodeHierarchy pe
where pe.PARENTID = tree.ID
   or pe.id = tree.id

但在PostgreSQL中，我目前的查询无法实现相同效果，缺失了追踪linkedTargetId并获取其对应子节点的逻辑，当前查询如下：

WITH RECURSIVE tree AS (
    SELECT p.id, p.parentId, p.linkedTargetId
    FROM NodeHierarchy p
    WHERE p.id = 6

    UNION ALL

    SELECT child.id, child.parentId, child.linkedTargetId
    FROM NodeHierarchy child
             INNER JOIN tree t ON t.id = child.parentId
) cycle id set is_cycle using path
SELECT DISTINCT pe.*
FROM tree
         JOIN NodeHierarchy pe
              ON pe.parentId = tree.id ;

需要补充linkedTargetId的追踪逻辑，且实际数据中可能存在循环链接链。

解决方案 #

以下是修改后的PostgreSQL递归查询，同时处理直接子节点和链接目标的子节点，并通过路径检测避免循环：

WITH RECURSIVE tree AS (
    -- 初始节点：从id=6开始，记录访问路径用于循环检测
    SELECT 
        p.id, 
        p.parentId, 
        p.linkedTargetId,
        ARRAY[p.id] AS path
    FROM NodeHierarchy p
    WHERE p.id = 6

    UNION ALL

    -- 递归迭代：同时处理两种关联关系
    SELECT 
        child.id, 
        child.parentId, 
        child.linkedTargetId,
        t.path || child.id AS path
    FROM NodeHierarchy child
    JOIN tree t ON 
        -- 情况1：当前节点的直接子节点
        t.id = child.parentId 
        -- 情况2：当前节点linkedTargetId指向节点的子节点
        OR t.linkedTargetId = child.parentId
    -- 避免循环：当前节点未在已访问路径中出现
    WHERE NOT child.id = ANY(t.path)
)
-- 获取所有相关节点：递归树中的节点及其子节点
SELECT DISTINCT pe.*
FROM tree
JOIN NodeHierarchy pe ON 
    pe.id = tree.id 
    OR pe.parentId = tree.id;

逻辑说明 #

1. 初始递归节点：从指定节点（id=6）启动，用数组记录访问路径，用于后续循环检测。
2. 递归关联逻辑：同时处理两种层级关系，既包含当前节点的直接子节点，也包含当前节点linkedTargetId指向节点的子节点。
3. 循环防护：通过NOT child.id = ANY(t.path)判断当前节点是否已被访问，避免因循环链接导致的死循环。
4. 结果输出：最终返回递归树中所有节点，以及这些节点的直接子节点，与Oracle查询结果一致。

示例完整SQL脚本 #

CREATE TABLE NodeHierarchy
(
    id             INT PRIMARY KEY,
    name           VARCHAR(50),
    parentId       INT,
    linkedTargetId INT,
    FOREIGN KEY (parentId) REFERENCES NodeHierarchy (id),
    FOREIGN KEY (linkedTargetId) REFERENCES NodeHierarchy (id)
);

INSERT INTO NodeHierarchy (id, name, parentId, linkedTargetId)
VALUES (1, 'A', NULL, NULL);
INSERT INTO NodeHierarchy (id, name, parentId, linkedTargetId)
VALUES (2, 'B', 1, NULL);
INSERT INTO NodeHierarchy (id, name, parentId, linkedTargetId)
VALUES (3, 'C', 2, NULL);
INSERT INTO NodeHierarchy (id, name, parentId, linkedTargetId)
VALUES (4, 'D', 2, NULL);
INSERT INTO NodeHierarchy (id, name, parentId, linkedTargetId)
VALUES (5, 'E', 3, NULL);
INSERT INTO NodeHierarchy (id, name, parentId, linkedTargetId)
VALUES (6, 'F', 4, NULL);
INSERT INTO NodeHierarchy (id, name, parentId, linkedTargetId)
VALUES (7, 'G', 3, NULL);
INSERT INTO NodeHierarchy (id, name, parentId, linkedTargetId)
VALUES (8, 'H', 6, 3);
INSERT INTO NodeHierarchy (id, name, parentId, linkedTargetId)
VALUES (9, 'I', 6, NULL);

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