postgresql - 合并多个表

Question

我试图遵循这个答案

Dynamic UNION ALL query in Postgres

但是我越来越 错误:“record”处或附近的语法错误

DECLARE
    rec record;
    strSQL text; 
BEGIN
    FOR
        rec in 
                select table_name
                from 
                    information_schema.tables
                where
                    table_name like 'history%' 

        loop
                strSQL : = strSQL || 'Select * from' || rec.table_schema ||'.'|| rec.table_name || ' UNION '; 
        end loop; 
 -- remove last ' UNION ' from strSQL  

--strSQL := 'Select row_number() over(order by rowid ) as row_num from (' || strSQL || ')';



execute strSQL;

有人有什么想法吗？

背景:历史表每晚都会移动到它自己的表中，并附加日期。

所以每个表名都是history04242018，有没有更好的办法获取多天的数据？

编辑:表总是有相同数量的列，所以并集应该没问题

edit2:我只有读取权限。

更新根据使用匿名代码块的建议，我现在使用以下代码:

DO   
    $$
declare
  strSQL text;
begin
  select
    string_agg(format('select * from %I.%I', table_schema, table_name), E' union\n')
    into strSQL
  from information_schema.tables
  where table_name like 'history%';


   execute strSQL ;
end $$;

但是我现在得到了错误

Describe Error: Failed to retrieve EXPLAIN plan(s): ERROR: syntax error at or near "DO" Position: 58

0 record(s) affected

Answer 1

declare、for、loop、execute 是 plpgsql 的部分, 不是普通的 sql (declare 可以在普通的 sql 中使用，但含义不同)。所以你应该把你的代码包装成 anonymous block或输入 function如果你想从中返回一些数据:

create function get_history(p_day int)
  returns table (<structure of history tables here>)
  -- or
  -- returns setof <history table name>
  language plpgsql
as $$
declare
  strSQL text;
begin
  select
    string_agg(format('select * from %I.%I', table_schema, table_name), E' union\n')
    into strSQL
  from information_schema.tables
  where table_name like to_char(p_day, '"history__"FM09%');

  return query execute strSQL;
end $$;

另请参阅 Table partitioning (在文章顶部选择您的 PostgreSQL 版本)。

---

更新

然而，有几种方法可以在不更改数据库架构的情况下从匿名 plpgsql block 返回查询数据:cursors和 prepared statements .

IMO 第二个更简单一点，所以:

do $$
declare
  strSQL text;
begin
  select
    string_agg(format('select * from %I.%I', table_schema, table_name), E' union\n')
    into strSQL
  from information_schema.tables
  where table_name like to_char(p_day, '"history__"FM09%');

  -- Prepend "prepare", change the "foo" name as you wish
  strSQL := 'prepare foo as ' || strSQL;

  execute strSQL;
end $$;

-- Usage
execute foo;

-- And deallocate prepared statement when it does not need anymore:
deallocate foo;

Simple working example