PostgreSQL 13递归CTE优化：改写为PL/pgSQL WHILE LOOP存储过程

以下是基于你提供的递归CTE逻辑，改写后的PL/pgSQL存储过程，通过WHILE循环迭代替代递归CTE，同时优化了重复数据插入问题，提升执行效率：

CREATE OR REPLACE PROCEDURE get_org_structure()
LANGUAGE plpgsql
AS $$
DECLARE
    inserted_rows INT;
BEGIN
    -- 创建临时表存储迭代中间结果，结构与原递归CTE一致
    CREATE TEMP TABLE IF NOT EXISTS relations_temp (
        pos_id INT,
        boss_pos_id INT,
        level_num INT,
        link_type INT,
        link_type_array INT[],
        pos_id_array INT[],
        PRIMARY KEY (pos_id, boss_pos_id, level_num) -- 主键约束避免重复插入，加速后续关联
    ) ON COMMIT DROP;

    -- 插入迭代的初始数据（对应递归CTE的基例）
    INSERT INTO relations_temp
    SELECT
        l.pos_id,
        l.boss_pos_id,
        1 AS level_num,
        l.link_type,
        l.link_type_array,
        l.pos_id_array
    FROM temp_loop l
    ON CONFLICT DO NOTHING;

    -- 获取初始插入行数，作为循环启动条件
    GET DIAGNOSTICS inserted_rows = ROW_COUNT;

    -- WHILE循环迭代扩展组织结构层级
    WHILE inserted_rows > 0 LOOP
        -- 从已有层级数据关联职位关系表，生成新层级记录并插入临时表
        INSERT INTO relations_temp
        SELECT
            l.pos_id,
            l.boss_pos_id,
            r.level_num + 1 AS level_num,
            l.link_type,
            r.link_type_array || l.link_type AS link_type_array,
            r.pos_id_array || l.pos_id AS pos_id_array
        FROM temp_pos_boss_with_min_link l
        JOIN relations_temp r
            ON l.pos_id = r.boss_pos_id
            AND l.pos_id <> ALL(r.pos_id_array) -- 防止循环引用，与原递归逻辑一致
        ON CONFLICT DO NOTHING; -- 跳过已存在的记录，避免重复

        -- 更新本次插入的行数，判断是否继续循环
        GET DIAGNOSTICS inserted_rows = ROW_COUNT;
    END LOOP;

    -- 输出最终结果，逻辑与原递归CTE的SELECT完全一致
    SELECT DISTINCT
        pos_id_array[1] AS pos_id,
        boss_pos_id AS boss_pos_id,
        level_num AS level_id,
        pos_id AS pos_original_id,
        CASE
            WHEN ARRAY[1, 2] <@ link_type_array THEN 0
            WHEN ARRAY[1] <@ link_type_array THEN 1
            ELSE 2
        END AS link_type
    FROM relations_temp;
END;
$$;

使用说明 #

调用存储过程即可生成组织结构数据：

CALL get_org_structure();

优化点说明 #

1. 临时表加主键约束：避免重复插入相同层级的记录，同时加速循环中的关联查询。
2. ON CONFLICT DO NOTHING：减少无效插入操作，提升迭代效率。
3. WHILE循环迭代：替代递归CTE的隐式递归，对大数据量场景的执行计划更可控，减少递归深度限制的影响。

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