Oracle递归SQL查询转PostgreSQL（含CONNECT BY、NVL等语法）

以下是与你提供的Oracle查询功能完全等价的PostgreSQL递归查询：

WITH struct_data AS (
    -- 预先获取结构化数据，避免重复调用函数
    SELECT
        id,
        parent,
        my_id,
        name,
        my_type,
        order_by_struct(name, my_type) AS sort_key
    FROM test_tbl.struct_filter(
        :ObjectTypeId,
        :FilterTypeId,
        NULL,
        'adm'
    )
),
RECURSIVE tree_hierarchy AS (
    -- 锚点成员：起始节点（对应Oracle的START WITH）
    SELECT
        id AS treeid,
        parent AS parentid,
        my_id AS entityid,
        name AS name,
        1 AS level,
        ARRAY[sort_key] AS sort_path
    FROM struct_data
    WHERE id = :NodeId

    UNION ALL

    -- 递归成员：遍历子节点（对应Oracle的CONNECT BY PRIOR id=parent）
    SELECT
        d.id AS treeid,
        d.parent AS parentid,
        d.my_id AS entityid,
        d.name AS name,
        th.level + 1 AS level,
        th.sort_path || d.sort_key AS sort_path
    FROM struct_data d
    JOIN tree_hierarchy th ON d.parent = th.treeid
)
-- 最终查询：应用层级过滤并按同级节点排序
SELECT treeid, parentid, entityid, name
FROM tree_hierarchy
WHERE level = COALESCE(:Lvl, level)
ORDER BY sort_path;

关键说明： #

• 预加载结构化数据：使用struct_data CTE一次性调用struct_filter函数，避免递归过程中重复调用，提升效率且保证数据一致性。
• 手动跟踪层级：PostgreSQL没有Oracle内置的level列，因此我们在递归CTE中显式维护level字段，锚点节点为层级1，每递归一层递增1。
• 实现同级节点排序：通过sort_path数组存储每个节点的排序键（由order_by_struct生成），最终按这个数组排序，完全等价于Oracle的ORDER SIBLINGS BY行为——每个父节点的子节点都会按指定规则排序。
• 层级过滤逻辑：使用COALESCE(:Lvl, level)实现与OracleNVL(:Lvl, level)相同的效果：若传入:Lvl参数，则仅返回该层级的节点；若参数为NULL，则返回所有层级。
• 递归关联条件：通过d.parent = th.treeid实现父节点到子节点的遍历，对应Oracle的CONNECT BY PRIOR id=parent。

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