如何将该Oracle数据库SQL代码转换为PostgreSQL版本

你这段Oracle SQL的核心作用是检测alf_child_ASSOC节点关联表中的循环引用，PostgreSQL不支持原生CONNECT BY语法，需要通过递归CTE（WITH RECURSIVE）实现等价逻辑，转换后可直接运行的代码如下：

WITH RECURSIVE cycles AS (
    -- 锚点层：初始化所有遍历起点，等价于CONNECT BY的起始节点采集
    SELECT 
        ',' || child_node_id AS path,
        CASE 
            WHEN child_node_id < child_node_id THEN ',' || child_node_id
            ELSE ''
        END AS less_path,
        child_node_id AS root_node_id,
        child_node_id AS current_child_id,
        ARRAY[child_node_id] AS visited_nodes
    FROM alf_child_ASSOC

    UNION ALL

    -- 递归层：逐层遍历上下级关联，等价于CONNECT BY的层级迭代逻辑
    SELECT
        c.path || ',' || a.child_node_id AS path,
        CASE
            WHEN a.child_node_id < c.root_node_id THEN c.less_path || ',' || a.child_node_id
            ELSE c.less_path
        END AS less_path,
        c.root_node_id,
        a.child_node_id AS current_child_id,
        c.visited_nodes || a.child_node_id AS visited_nodes
    FROM cycles c
    INNER JOIN alf_child_ASSOC a 
        ON a.parent_node_id = c.current_child_id
    -- 等价于Oracle NOCYCLE：遇到已访问节点立即终止，避免无限递归
    WHERE a.child_node_id <> ALL(c.visited_nodes)
),
valid_cycles AS (
    SELECT path, less_path
    FROM cycles
    -- 等价于原SQL CONNECT_BY_ROOT parent_node_id = child_node_id 判定：遍历回到起点即形成环
    WHERE current_child_id = root_node_id
    -- 排除节点自身关联的无效记录
    AND array_length(visited_nodes, 1) > 1
)
SELECT *
FROM valid_cycles
-- 等价于原SQL LTRIM(less_path, ',') IS NULL 筛选逻辑：每个环仅返回一次，避免重复
WHERE LTRIM(less_path, ',') = '';

注意事项 #

• 上述写法通过数组记录访问路径实现环检测，兼容PostgreSQL 8.4及以上所有版本
• 如果使用PostgreSQL 14+版本，可以用原生CYCLE子句替换自定义数组检测逻辑，写法更简洁，性能也更好
• 字段逻辑和原SQL完全对齐：path字段返回环上所有节点的拼接串，和原SYS_CONNECT_BY_PATH返回格式完全一致

内容的提问来源于stack exchange，提问作者Sébastien Vallet