如何自动跨多个同结构用户Schema查询actions表并合并结果？

我当前的项目中，每个用户对应一个独立的Schema，命名格式为user_<用户ID>，每个Schema下都包含posts、likes和actions三张表，结构示例如下：

user_537        (schema)
    posts       (table)
    likes       (table)
    actions     (table)
user_538        (schema)
    posts       (table)
    likes       (table)
    actions     (table)
user_539        (schema)
    posts       (table)
    likes       (table)
    actions     (table)

需要实现跨所有用户Schema的actions表统计，最终得到类似以下手动拼接SQL的结果：

select 
    537 as user_id, 
    count(*) actions
from user_537.actions 
UNION ALL

select 
    538 as user_id, 
    count(*) actions
from user_538.actions 
UNION ALL

select 
    539 as user_id, 
    count(*) actions
from user_539.actions

现有通过information_schema生成SQL的方案存在两个问题：

• 需要手动复制生成的SQL语句再次执行
• 生成的SQL末尾会多出一个多余的UNION ALL，需要手动删除

方法一：使用动态SQL直接执行（PostgreSQL） #

利用PostgreSQL的EXECUTE语句结合字符串拼接，自动生成并执行完整的统计SQL，同时处理掉末尾的UNION ALL：

DO $$
DECLARE
    sql_text TEXT;
BEGIN
    -- 生成所有子查询并拼接，自动避免末尾多余UNION ALL
    SELECT string_agg(
        'SELECT ' || REPLACE(table_schema, 'user_', '') || ' as user_id, count(*) as actions FROM ' || table_schema || '.actions',
        ' UNION ALL '
    ) INTO sql_text
    FROM information_schema.tables
    WHERE table_schema LIKE 'user_%' AND table_name = 'actions';
    
    -- 执行生成的SQL
    EXECUTE sql_text;
END $$;

关键说明： #

• 使用string_agg函数自动拼接子查询，用UNION ALL作为分隔符，天然避免末尾出现多余拼接符
• 修正了原方案的替换逻辑：用REPLACE(table_schema, 'user_', '')提取用户ID，避免出现开头的下划线
• 增加table_name = 'actions'过滤条件，确保只针对目标表，避免其他表干扰

方法二：创建可复用的统计函数 #

如果需要多次执行该统计，可以创建一个函数直接返回结构化结果集：

CREATE OR REPLACE FUNCTION get_all_user_actions()
RETURNS TABLE(user_id INT, actions BIGINT) AS $$
DECLARE
    sql_text TEXT;
BEGIN
    SELECT string_agg(
        'SELECT ' || REPLACE(table_schema, 'user_', '') || '::INT as user_id, count(*)::BIGINT as actions FROM ' || table_schema || '.actions',
        ' UNION ALL '
    ) INTO sql_text
    FROM information_schema.tables
    WHERE table_schema LIKE 'user_%' AND table_name = 'actions';
    
    RETURN QUERY EXECUTE sql_text;
END $$ LANGUAGE plpgsql;

使用时直接调用即可：

SELECT * FROM get_all_user_actions();

关键说明： #

• 函数返回明确类型的结果集，确保user_id为整数、actions为大整数，避免类型不一致问题
• 可重复调用，适合频繁执行统计的场景

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