PostgreSQL同模式多结构一致表合并视图创建求助

要实现自动合并指定schema下所有同结构表、新增customer列的视图，可以通过PL/pgSQL函数生成动态SQL来完成，具体步骤如下：

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

这个函数会自动获取目标schema下的所有表，拼接出合并查询语句并创建视图：

CREATE OR REPLACE FUNCTION create_unified_view(p_schema text, p_view_name text)
RETURNS void AS $$
DECLARE
    v_sql text;
BEGIN
    -- 生成所有表的UNION ALL查询语句，同时拼接customer列
    SELECT string_agg(
        format(
            'SELECT "timestamp", company, division, team, request, value, source, ' ||
            'concat(company, ''-'', division, ''-'', team) AS customer FROM %I.%I',
            schemaname, tablename
        ),
        ' UNION ALL '
    ) INTO v_sql
    FROM pg_catalog.pg_tables
    WHERE schemaname = p_schema;

    -- 如果没有找到表，抛出提示
    IF v_sql IS NULL THEN
        RAISE EXCEPTION 'No tables found in schema %', p_schema;
    END IF;

    -- 创建或替换视图
    EXECUTE format('CREATE OR REPLACE VIEW %I.%I AS %s', p_schema, p_view_name, v_sql);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

2. 调用函数创建视图 #

执行以下SQL，替换my_schema为你的目标schema名称，unified_data_view为你想要的视图名称：

SELECT create_unified_view('my_schema', 'unified_data_view');

3. 注意事项 #

• 视图是静态的：如果后续有新表加入目标schema，需要重新调用上述函数来更新视图，否则新表的数据不会被包含进去。
• 空值处理：如果company、division或team存在空值，concat函数会返回空字符串。若需要保留非空部分的拼接，可以改用concat_ws('-', company, division, team)（该函数会自动忽略空值）。
• 权限：函数使用SECURITY DEFINER，确保执行用户有足够权限访问目标schema下的表以及创建视图。

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