主页 / dba / 问题 / 71432
Accepted
dd_a
Asked: 2014-07-15 04:19:40 +0800 CST

如何使表名成为 PostgreSQL 中的属性？

  • 772

我在 PostgreSQL 中收到了数百个结构相同的表，每个表都包含不同日期的相似数据，因此表名是数据的日期。

我想将它们全部合并到一个表中，但不会丢失数据日期这一非常关键的信息。我想我应该以某种方式使用information_schema.tables和使用我不知道的PL/pgsql，因此应该学习，但老实说，这对于一次性操作来说似乎是一个很大的努力。所以，我的问题是：
有没有一种简单的方法可以实现这一目标，或者最好的解决方案是什么？

postgresql plpgsql

2 个回答

  1. Best Answer

    如果您想拥有一些自动执行此操作的魔法，您可以使用一个动态创建 select 语句并返回所有表的联合的函数：

    create or replace function get_all()
  returns table (data_source text, id integer)
as
$body$
declare
   full_query text := '';
   table_rec  record;
   row_nr     integer := 1;
begin
   for table_rec in select table_name 
                    from information_schema.tables 
                    where table_schema = 'public'     -- adjust here for your schema
                      and table_name like 'table_%'   -- adjust here for your prefix
                    order by table_name
   loop
     if row_nr > 1 then  
        full_query := full_query || ' union all ';
     end if;
     full_query := full_query || ' select '||quote_literal(table_rec.table_name)::text||' as data_source, t.id FROM '||quote_ident(table_rec.table_name)::text||' as t';
     row_nr := row_nr + 1;
   end loop;
   return query execute full_query;
end;
$body$
language plpgsql;

    然后可以像这样使用上面的函数：

    select *
from get_all();

    你会得到这样的东西：

    data_source         id
table_2014-07_01    ..
table_2014-07_01    ..
.....
table_2014-07_02    ...
table_2014-07_02    ...

    如果您想要“data_source”列的不同值，只需在函数内部进行一些模式匹配/替换。

    另一种选择是将函数更改为（重新）创建视图（而不是直接返回数据）。这可以使检索更快一些。

    您还可以（重新）使用动态 SQL 创建物化视图，以加快检索速度（因为结果会出现在可以正确索引的单个“表”中）。

    • 3

  2. 这可以相当简单和快速：

    DO
$do$
BEGIN
EXECUTE (
   SELECT E'CREATE TABLE t_new AS\n'
          || string_agg(format('SELECT tableoid::regclass AS source_table, * FROM %s'
                              , c.oid::regclass::text)
                       ,E'\nUNION ALL\n' ORDER BY c.oid::regclass::text)
   FROM   pg_namespace n
   JOIN   pg_class c ON c.relnamespace = n.oid
   WHERE  n.nspname = 'public'  -- your schema here
   AND    c.relkind = 'r'       -- only tables
   AND    c.relname LIKE 't_%'  -- your table pattern here
   );
END
$do$;

    替换*为您想要的列。生成并执行一次这种形式的查询：

    CREATE TABLE t_new AS
SELECT tableoid::regclass AS source_table, * FROM t_20140707
UNION ALL
SELECT tableoid::regclass AS source_table, * FROM t_20140708
UNION ALL
SELECT tableoid::regclass AS source_table, * FROM t_20140709
...

    因此，您会得到一张新表，其中包含所有内容以及源表名称。

    解释

    • DO语句是一次性操作的不错选择。您不能从中返回值，但可以创建一个新表。您可能想使用一个TEMP TABLE...

    • tableoid::regclass用于检索源表的（正确转义的）表名，并且在各种情况下都非常方便。更多详细信息：
      获取表的所有分区名称

    • 我在单个元查询中构建查询而不是循环，这几乎总是更快更干净（如果可能的话）。相关答案：
      遍历模式中的类似表

    • 对目录表的查询通常要快得多，并提供 Postgres 实际拥有的一切。例如oid我在这里使用的系统列。除此之外，使用信息模式也有优缺点。大多数情况下，实现跨主要版本和各种 RDBMS 的一些可移植性是有用的。可移植性似乎与一次性操作无关，而且根据我的经验，它几乎无法正常工作。更多内容：
      如何检查给定模式中是否存在表

    • 3

相关问题