PostgreSQL动态行转列：按destination_type创建视图需求

需求：为每个destination_type创建视图，将destination_type_settings表的settings_name字段转为列，列值优先取destination_setting_value表的val字段，若val为空则使用destination_type_settings表的default_val字段。静态PIVOT需要提前指定列，无法适配未知数量的设置项，因此需用动态SQL实现。

现有基础查询 #

已完成的关联查询（补充了默认值逻辑）：

select 
    dest.destination_name, dest."host", dest.port, dest.protocol, dest.regexp, dest.active,
    dts.settings_name, COALESCE(dsv.val, dts.default_val) as setting_value
from 
    "public".destination as dest
join 
    "public".destination_type as dt on dt."id" = dest.destination_type_fk
join 
    "public".destination_type_settings as dts on dts.destination_type_fk = dt."id"
left join 
    "public".destination_setting_value as dsv on dsv.destination_type_settings_fk = dts."id" 
                                              and dsv.destination_fk = dest."id"
where dt."id" = 7

解决方案：动态生成视图（PostgreSQL） #

利用PL/pgSQL编写函数，自动遍历所有目标类型并生成对应透视视图：

1. 创建生成视图的函数 #

CREATE OR REPLACE FUNCTION generate_destination_type_views()
RETURNS void AS $$
DECLARE
    rec record;
    cols text;
    view_name text;
BEGIN
    -- 遍历每个目标类型
    FOR rec IN SELECT id, destination_type_name FROM destination_type LOOP
        -- 生成视图名称，避免命名冲突
        view_name := 'v_destination_' || lower(replace(rec.destination_type_name, ' ', '_'));
        
        -- 动态生成透视列的SQL片段
        SELECT string_agg(
            format(
                'MAX(CASE WHEN dts.settings_name = %L THEN COALESCE(dsv.val, dts.default_val) END) AS %I',
                settings_name, settings_name
            ),
            ', '
        ) INTO cols
        FROM destination_type_settings
        WHERE destination_type_fk = rec.id;
        
        -- 执行创建视图的动态SQL
        EXECUTE format(
            'CREATE OR REPLACE VIEW %I AS
             SELECT 
                 dest.destination_name, dest.host, dest.port, dest.protocol, dest.regexp, dest.active,
                 %s
             FROM destination dest
             JOIN destination_type dt ON dt.id = dest.destination_type_fk
             LEFT JOIN destination_type_settings dts ON dts.destination_type_fk = dt.id
             LEFT JOIN destination_setting_value dsv ON dsv.destination_type_settings_fk = dts.id AND dsv.destination_fk = dest.id
             WHERE dt.id = %L
             GROUP BY dest.id, dest.destination_name, dest.host, dest.port, dest.protocol, dest.regexp, dest.active',
            view_name, cols, rec.id
        );
        
        RAISE NOTICE '已创建/更新视图: %', view_name;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

2. 执行函数生成视图 #

SELECT generate_destination_type_views();

针对测试数据中的http类型，会生成名为v_destination_http的视图。

3. 查询生成的视图 #

SELECT * FROM v_destination_http;

返回结果示例：

destination_name |   host    | port | protocol | regexp | active | added_setting_1 | added_setting_2
-------------------+-----------+------+----------+--------+--------+-----------------+-----------------
 dest 1            | localhost | 1234 | http     | ^.*+   | f      | val 1_1         | val 1_2
 dest 2            | 0.0.0.0   | 2222 | http     | .*     | t      | val 2_1         | val 2_2

4. 后续维护 #

若新增目标类型或修改某类型的设置项，只需重新执行SELECT generate_destination_type_views();即可更新对应视图。

关键逻辑说明 #

• 用MAX(CASE...)实现透视：每个目标与设置项的组合仅一条记录，MAX不影响结果，同时满足分组要求。
• COALESCE(dsv.val, dts.default_val)实现优先取自定义值、无自定义值则用默认值的逻辑。
• 视图名称自动生成：以v_destination_为前缀，配合小写化并替换空格的类型名，避免命名冲突。

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