PostgreSQL动态生成类继承关系访问矩阵的方法

静态SQL无法实现自适应行数的动态列矩阵输出，因为SQL语法要求查询返回的列结构必须在执行规划阶段就确定，要适配class表的行数动态变化，使用PL/pgSQL编写动态SQL函数是最直接的方案。

实现逻辑 #

• 自动遍历class表所有类ID，动态拼接每个ID对应的继承关系判断列，逻辑和手写的静态CASE判断规则完全一致
• 用交叉连接+左关联的写法替代多层嵌套IN子查询，执行效率更高
• 最终执行拼接完成的动态SQL，返回全量访问矩阵

具体PL/pgSQL实现代码 #

CREATE OR REPLACE FUNCTION get_class_inheritance_matrix()
RETURNS SETOF RECORD
AS $$
DECLARE
    col_concat text;
    exec_sql text;
BEGIN
    -- 按ID顺序拼接所有类对应的矩阵列
    SELECT string_agg(
        format(
            'MAX(CASE WHEN src.id = inh.source_id THEN 1 ELSE 0 END) AS "%s"',
            cls.id
        ), ', ' ORDER BY cls.id
    ) INTO col_concat
    FROM class cls;

    -- 构造最终查询语句
    exec_sql := format(
        'SELECT tgt.id, tgt.name, %s
         FROM class tgt
         CROSS JOIN class src
         LEFT JOIN inheritance inh 
             ON inh.target_id = tgt.id 
             AND inh.source_id = src.id
         GROUP BY tgt.id, tgt.name
         ORDER BY tgt.id',
        col_concat
    );

    -- 执行动态SQL返回结果
    RETURN QUERY EXECUTE exec_sql;
END;
$$ LANGUAGE plpgsql STABLE;

调用方式 #

因为函数返回动态列结构，调用时自动适配列定义即可，不需要手动修改列数：

DO $$
DECLARE
    col_list text;
BEGIN
    -- 自动生成匹配当前class表的列定义
    SELECT string_agg(format('"%s" integer', id), ', ' ORDER BY id)
    INTO col_list FROM class;

    -- 执行查询将结果存入临时表
    EXECUTE format(
        'CREATE TEMP TABLE temp_matrix AS
         SELECT * FROM get_class_inheritance_matrix() AS t(id bigint, name varchar(500), %s)',
        col_list
    );
END $$;

-- 查询临时表获取最终访问矩阵
SELECT * FROM temp_matrix;

-- 用完可清理临时表
-- DROP TABLE temp_matrix;

补充说明 #

• 输出矩阵中，行对应被继承的目标类、列对应源类，值为1表示两个类之间存在直接继承关系，值为0表示无直接继承关系
• 如果需要计算包含多层继承的传递性访问关系，只需要把SQL中关联的inheritance表替换为递归CTE生成的全继承链路关系即可
• 当class表新增/删除类记录时，不需要修改任何代码，函数会自动适配生成对应列数的矩阵

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