Union multiple tables

Question

I tried to follow this answer

Dynamic UNION ALL query in Postgres

but I am getting ERROR: syntax error at or near "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;

anyone have any ideas?

background: history table is moved every night to its own table with the date appended.

so history04242018 for each table name, any better way to get the data for multiple days?

edit: tables will always have the same amount of columns so the union should be fine

edit2: I only have read access.

update with the suggestion of using an anonymous code block I am now using the following:

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 $$;

however I now get the error

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 are the parts of the plpgsql, not plain sql (declare could be used in the plain sql but in different meaning). So you should to wrap your code into anonymous block or into function if you want to return some data from it:

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 $$;

Also look at the Table partitioning (choose your PostgreSQL version at the top of article).

---

Update

However there are several ways to return query data from anonymous plpgsql block without changing DB schema: cursors and prepared statements.

IMO second one is simpler a bit, so:

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