时间戳

首页 > 解决方案 > 联合不同的表

sql - 联合不同的表

问题描述

以下函数动态连接不同的表。

create or replace function unified_tables() returns table(  
                      1         TEXT
                    , 2         TEXT
                    , 3         TEXT
                    , 4         TEXT
                    , 5         JSONB
                    , 6         BIGINT
                    , 7         BIGINT
                    , 8         TEXT
                    , 9         TEXT
                    , 10        TIMESTAMPTZ
                    )
as
$$
declare
  a record;
begin
  for a in select table_schema
                    from information_schema.tables
                    where table_name = 'name' 
  loop
     return query 
        execute format('select  %L as source_schema,  *  from %I.name', a.table_schema,  a.table_schema);
  end loop;
end;
$$
language plpgsql;

不幸的是，并非所有调用的表都具有 RETURNS TABLE 中指定的所有列。

准确地说，有 15 个表（循环超过 200 多个表）缺少第 2 列，两个表缺少第 4 列，还有 5 个表缺少第 9 列。

将来进入循环的表也可能会丢失列。我无法控制源结构。

如何继续使用为缺少的列添加空值的函数以维护 RETURNS TABLE 中定义的结构？

标签： sqlpostgresql

解决方案

您可以为此创建一个集合返回函数：

create function get_all_pages()
  returns table (....) 
as
$$
declare
  l_info_rec record;
begin
  for l_info_rec in select table_schema
                    from information_schema.tables
                    where table_name = 'page'
  loop
     return query 
        execute format('select %L as source_schema, * 
                       from %I.page', l_info_rec.table_schema, l_info_rec.table_schema);
  end loop;
end;
$$
language plpgsql;

然后运行：

select *
from get_all_pages();

return query在 PL/pgSQL 函数中不会结束函数。它只是将查询的结果附加到函数的结果中。

您可以选择任何表作为返回类型，在这种情况下它只是用作“占位符”（再次：假设所有表都是 100% 相同的）。或者，您可以使用returns table (....)- 但这将要求您手动列出表的所有列。

请注意，这将在函数返回之前在服务器上缓冲完整的结果，因此这可能不适合非常大的表。

另一种选择是创建一个事件触发器，该触发器在每次创建新表或删除现有表时重新创建一个 VIEW（执行 UNION ALL）。

推荐阅读