PostgreSQL递归查询资源所有父级mask并聚合的实现方法

涉及表结构 #

资源表 resources.resource #

CREATE TABLE resources.resource (
    id uuid NOT NULL,
    children jsonb NULL,
    ctx varchar(255) NULL,
    parentclass varchar(255) NULL,
    parentid uuid NULL,
    resourceclass varchar(255) NULL,
    resourcetype varchar(255) NULL,
    status varchar(50) NULL,
    CONSTRAINT resource_pkey PRIMARY KEY (id)
);

用户资源掩码表 resources.userresourcemask #

CREATE TABLE resources.userresourcemask (
    id int8 NOT NULL,
    mask varchar(50) NULL,
    username varchar(255) NULL,
    resource_id uuid NULL,
    CONSTRAINT userresourcemask_pkey PRIMARY KEY (id)
);
-- 外键关联资源表
ALTER TABLE resources.userresourcemask ADD CONSTRAINT fk5vjgr74nhjgovctjtt2qifsu7 FOREIGN KEY (resource_id) REFERENCES resources.resource(id);

需求说明 #

针对全量资源，递归向上遍历所有父层级节点，收集每个节点关联的掩码，拼接为逗号分隔的字符串返回，格式如下：

实现SQL #

基于PostgreSQL递归CTE实现，遍历过程中用数组收集掩码，最终聚合去重后拼接：

WITH RECURSIVE res_hierarchy AS (
    -- 锚点：初始化全量资源作为遍历起点
    SELECT 
        r.id AS root_resource_id,
        r.id AS current_node_id,
        r.parentid,
        ARRAY[]::varchar[] AS collected_masks
    FROM resources.resource r

    UNION ALL

    -- 递归部分：向上查找父节点，收集对应掩码
    SELECT 
        rh.root_resource_id,
        p.id AS current_node_id,
        p.parentid,
        rh.collected_masks || COALESCE(ARRAY_AGG(u.mask) FILTER (WHERE u.mask IS NOT NULL), ARRAY[]::varchar[])
    FROM res_hierarchy rh
    INNER JOIN resources.resource p 
        ON rh.parentid = p.id
    LEFT JOIN resources.userresourcemask u 
        ON u.resource_id = p.id
    GROUP BY rh.root_resource_id, p.id, p.parentid, rh.collected_masks
)
-- 聚合结果，去重后拼接掩码字符串
SELECT 
    root_resource_id AS id,
    (SELECT parentid FROM resources.resource WHERE id = root_resource_id) AS parentid,
    STRING_AGG(DISTINCT mask, ', ' ORDER BY mask) AS mask
FROM res_hierarchy, UNNEST(collected_masks) AS mask
GROUP BY root_resource_id;

调整说明 #

• 如果只需要查询单个指定资源的继承掩码，在锚点查询后加WHERE r.id = '目标资源UUID'即可，无需扫描全表
• 如果需要把资源自身配置的掩码也计入结果，在锚点部分关联掩码表，将自身掩码初始化到collected_masks数组即可
• 掩码拼接顺序可通过STRING_AGG的ORDER BY子句自定义调整
• 若不需要掩码去重，去掉DISTINCT关键字即可

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