如何遍历数据库所有schema执行指定SQL并将结果汇总存入public schema

步骤1：在public schema创建结果汇总表 #

你可以直接用已有查询快速生成匹配的表结构，额外新增schema_name字段标识数据来源：

CREATE TABLE IF NOT EXISTS public.all_schema_referral_result AS
SELECT ''::text AS schema_name, *
FROM p148.referral e
JOIN p148.contact_accounts ca ON e.contact_account_id = ca.id
JOIN p148.owner1 o ON o."name" = e.name
WHERE 1=2; -- 仅生成表结构，不插入测试数据

步骤2：执行动态脚本遍历所有schema并汇总结果 #

以下PL/pgSQL匿名块会自动遍历所有非系统schema，执行你的查询逻辑并将结果插入公共结果表：

DO $$
DECLARE
    current_schema text;
    -- SQL查询模板，%I为schema名占位符
    query_template text := '
        WITH referral AS (
            SELECT (date_trunc(''week'', soc_date))::date AS year_week, *
            FROM %I.referral e
            JOIN %I.contact_accounts ca ON e.contact_account_id = ca.id
            WHERE soc_date >= ''2018-01-01''
              AND soc_date <= ''2021-09-30''
        ),
        owner AS (
            SELECT * FROM %I.owner1
        )
        SELECT $1, *
        FROM referral ra
        JOIN owner o ON o."name" = ra.name
    ';
BEGIN
    -- 遍历所有业务schema，可自行添加过滤条件排除不需要处理的schema
    FOR current_schema IN
        SELECT schema_name
        FROM information_schema.schemata
        WHERE schema_name NOT IN ('pg_catalog', 'information_schema', 'public')
          -- 可选过滤：比如只处理p开头的业务schema：AND schema_name LIKE 'p%'
    LOOP
        -- 动态执行查询并插入结果表
        EXECUTE format(
            'INSERT INTO public.all_schema_referral_result ' || query_template,
            current_schema, current_schema, current_schema
        ) USING current_schema;
        RAISE NOTICE '已完成schema % 的数据汇总', current_schema;
    END LOOP;
END $$;

注意事项 #

• 如果owner1是全局存放在public schema的公共表，只需把查询模板里的%I.owner1修改为public.owner1，并删除format参数中最后一个current_schema即可。
• 执行前请确认所有待处理schema下的referral、contact_accounts表结构完全一致，否则会触发字段类型不匹配报错。
• 建议先添加schema过滤条件测试1-2个schema，确认结果符合预期后再全量执行。
• 数据量较大的场景可以在循环中添加分批提交逻辑，避免长事务占用资源。

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