Postgres递归CTE实现全表生成MasterIds数组的问题

需要为PostgreSQL中的location表每一行生成包含自身及所有上级节点ID的MasterIds数组，已创建如下临时表：

create temp table location as
select 1 as id,'World' as name,null as main_id
union all
select 2,'Asia',1
union all
select 3,'Africa',1
union all
select 4,'India',2
union all
select 5,'Sri Lanka',2
union all
select 6,'Mumbai',4
union all
select 7,'South Africa',3
union all
select 8,'Cape Town',7;

表结构 #

预期结果 #

需要为每行新增MasterIds Arrayint列，内容为从根节点到当前节点的ID数组：

当前问题 #

目前编写的递归CTE仅能处理单一行：

with recursive
    cte as (
    select * from location  where id=4
    union all
    select g.*  from cte join location g on g.id  = cte.main_id
    )
    select array_agg(id)  from cte l

解决方案 #

方案一：从根节点向下遍历（高效推荐）

从根节点开始逐层向下关联子节点，直接构建从根到当前节点的路径数组，执行效率更高：

WITH RECURSIVE hierarchy AS (
    -- 初始步骤：选中根节点，路径数组初始化为自身ID
    SELECT 
        id, 
        name, 
        main_id, 
        ARRAY[id] AS master_ids
    FROM location
    WHERE main_id IS NULL
    UNION ALL
    -- 递归步骤：关联子节点，将子节点ID追加到父节点路径末尾
    SELECT 
        l.id, 
        l.name, 
        l.main_id, 
        h.master_ids || l.id
    FROM location l
    JOIN hierarchy h ON l.main_id = h.id
)
SELECT id, name, main_id, master_ids
FROM hierarchy
ORDER BY id;

方案二：从每个节点向上追溯（灵活适配）

适配存在孤立节点或需要从任意节点启动递归的场景，从每个节点出发向上查找父节点，最终筛选出包含所有上级节点的完整路径：

WITH RECURSIVE node_paths AS (
    -- 初始步骤：选中所有节点，路径数组初始化为自身ID
    SELECT 
        id, 
        name, 
        main_id, 
        ARRAY[id] AS master_ids
    FROM location
    UNION ALL
    -- 递归步骤：向上关联父节点，将父节点ID添加到路径开头
    SELECT 
        np.id, 
        np.name, 
        np.main_id, 
        ARRAY[l.id] || np.master_ids
    FROM node_paths np
    JOIN location l ON np.main_id = l.id
)
-- 筛选每个节点的最长路径（即完整的上级路径）
SELECT id, name, main_id, master_ids
FROM node_paths
WHERE array_length(master_ids, 1) = (
    SELECT MAX(array_length(master_ids, 1))
    FROM node_paths np
    WHERE np.id = node_paths.id
)
ORDER BY id;

内容的提问来源于stack exchange，提问作者Prasanna Kumar J