如何将Oracle的ORG_ORGANISATION_RECURSIV_V递归视图迁移至PostgreSQL

迁移后等效PostgreSQL代码 #

-- 如果需要强制创建视图（基表不存在时也能创建），可先执行下方语句
-- SET check_function_bodies = off;

CREATE OR REPLACE VIEW "ORG_ORGANISATION_RECURSIV_V" (
    "NLEVEL", 
    "CORGANISATION_TYP_LEVEL", 
    "CORGANISATION_TYP_PATH", 
    "CORGANISATION_TYP_PATH_ID", 
    "NID_ROOT", 
    "NDEBITOR_KOMPOSITUM", 
    "NID"
) AS 
WITH RECURSIVE org_recursive AS (
    -- 锚点：所有节点作为递归起点，对应Oracle无START WITH的CONNECT BY逻辑
    SELECT
        1 AS nlevel,
        lpad('.', 1, '*') || o1.corganisation_typ AS corganisation_typ_level,
        o1.corganisation_typ,
        ',' || o1.corganisation_typ::text AS typ_path_tmp,
        ',' || o1.nid::text AS id_path_tmp,
        o1.nid AS nid_root,
        o1.ndebitor_kompositum,
        o1.nid
    FROM org_organisation o1

    UNION ALL

    -- 递归遍历子节点
    SELECT
        r.nlevel + 1 AS nlevel,
        lpad('.', r.nlevel + 1, '*') || o1.corganisation_typ AS corganisation_typ_level,
        o1.corganisation_typ,
        r.typ_path_tmp || ',' || o1.corganisation_typ::text AS typ_path_tmp,
        r.id_path_tmp || ',' || o1.nid::text AS id_path_tmp,
        r.nid_root,
        o1.ndebitor_kompositum,
        o1.nid
    FROM org_recursive r
    INNER JOIN org_organisation o1 
        ON r.nid = o1.ndebitor_kompositum
    -- 可选配置：防止循环递归导致栈溢出，可根据实际业务的层级深度调整最大值
    WHERE r.nlevel < 100
)
SELECT
    nlevel,
    corganisation_typ_level,
    ltrim(typ_path_tmp, ',') AS "CORGANISATION_TYP_PATH",
    ltrim(id_path_tmp, ',') AS "CORGANISATION_TYP_PATH_ID",
    nid_root,
    ndebitor_kompositum,
    nid
FROM org_recursive
-- 等效Oracle ORDER SIBLINGS BY 逻辑：同根同层级节点按指定字段倒序排列
ORDER BY nid_root, nlevel, corganisation_typ DESC;

-- 如果之前修改了check_function_bodies参数，执行下方语句恢复默认值
-- SET check_function_bodies = on;

语法映射说明 #

• Oracle的FORCE VIEW：PostgreSQL没有原生FORCE语法，若需要在基表不存在时也能创建视图，可临时关闭check_function_bodies参数实现，常规场景下直接使用CREATE OR REPLACE VIEW即可
• 层级查询CONNECT BY：替换为PostgreSQL标准递归CTE语法WITH RECURSIVE
• 层级伪列LEVEL：在递归CTE中通过自定义层级字段累加实现
• SYS_CONNECT_BY_PATH：递归过程中逐层级拼接路径字符串，最后修剪掉开头多余的分隔符即可实现等效效果
• CONNECT_BY_ROOT：在递归锚点中保存根节点ID，后续递归过程直接继承该值即可
• ORDER SIBLINGS BY：在最终查询中按根节点ID、层级、排序字段排序，实现同级节点的排序规则

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