如何遍历PostgreSQL非系统表批量创建物化视图并跳过已存在视图

直接用PL/pgSQL编写存储过程实现批量逻辑，以下是完整代码和细节说明：

存储过程实现 #

CREATE OR REPLACE PROCEDURE batch_create_mv()
LANGUAGE plpgsql
AS $$
DECLARE
    rec_table RECORD;
    col_list TEXT;
    mv_name TEXT;
BEGIN
    -- 遍历所有非系统基表
    FOR rec_table IN
        SELECT table_schema, table_name
        FROM information_schema.tables
        WHERE table_schema NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
          AND table_type = 'BASE TABLE'
    LOOP
        -- 自定义物化视图命名规则（示例：mv_<schema>_<table>）
        mv_name := 'mv_' || rec_table.table_schema || '_' || rec_table.table_name;
        
        -- 检查物化视图是否已存在
        IF to_regclass(rec_table.table_schema || '.' || mv_name) IS NULL THEN
            -- 按原表列顺序生成列列表
            SELECT string_agg(column_name, ', ')
            INTO col_list
            FROM information_schema.columns
            WHERE table_schema = rec_table.table_schema
              AND table_name = rec_table.table_name
            ORDER BY ordinal_position;
            
            -- 动态执行创建语句
            EXECUTE format(
                'CREATE MATERIALIZED VIEW %I.%I AS SELECT %s FROM %I.%I',
                rec_table.table_schema,
                mv_name,
                col_list,
                rec_table.table_schema,
                rec_table.table_name
            );
            
            RAISE NOTICE '已创建物化视图: %.%', rec_table.table_schema, mv_name;
        ELSE
            RAISE NOTICE '物化视图已存在，跳过: %.%', rec_table.table_schema, mv_name;
        END IF;
    END LOOP;
END;
$$;

使用方法 #

执行存储过程启动批量创建：

CALL batch_create_mv();

关键细节说明 #

• 非系统表筛选：通过information_schema.tables排除PostgreSQL内置的系统schema，仅处理用户自定义的基表。
• 命名规则自定义：示例中用mv_<schema>_<table>命名，你可以直接修改mv_name的拼接逻辑（比如改为原表名加_mv后缀）。
• 跳过已存在对象：用to_regclass()函数检查物化视图是否存在，返回NULL则执行创建逻辑。
• 列结构一致性：通过string_agg()按原表的列顺序拼接列名，保证物化视图和原表列结构完全匹配。
• 安全的动态SQL：用format()函数的%I占位符处理标识符，自动适配带特殊字符的schema/表名，避免SQL注入风险。

注意事项 #

1. 执行用户需具备：
   • 目标schema下的CREATE MATERIALIZED VIEW权限
   • 读取information_schema系统表的权限
   • 原表的SELECT权限
2. 若需仅处理特定schema的表，可修改WHERE条件（比如添加AND table_schema = 'public'）。
3. 如果需要后续刷新物化视图，可在存储过程中追加REFRESH MATERIALIZED VIEW逻辑，或单独执行刷新语句。

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