SQL script to UNION a large number of tables

Question

I have to do a union of a large number of disjoint daily tables from 2012-12-17 to 2012-10-30 in this example. The code for this gets ugly here is the snippet:

CREATE table map 
with (appendonly=true, compresstype = quicklz)
AS
 SELECT * FROM final_map_12_17
    UNION ALL
 SELECT * FROM final_map_12_16
    UNION ALL
     SELECT * FROM final_map_12_15
    UNION ALL
     SELECT * FROM final_map_12_14
    UNION ALL
....
SELECT * FROM final_map_10_30;

Can I do this type of thing with a sequence or PL/PGSQL function instead of writing out each individual select by hand?

Answer 1

You can loop over date range in plpgsql function like this:

create or replace function add_map(date_from date, date_to date)
returns void language plpgsql as $$
declare
    day date;
begin
    for day in 
        select generate_series(date_from, date_to, '1 day')
    loop
        execute 'insert into map select * from final_map_'|| 
            to_char(extract(month from day), '09')|| '_' || 
            to_char(extract(day from day), '09');
    end loop;
end; $$;

Calling the function:

-- create table map (....);
select add_map('2012-11-30', '2012-12-02');

is equivalent to:

insert into map select * from final_map_11_30;
insert into map select * from final_map_12_01;
insert into map select * from final_map_12_02;

Answer 2

There isn't a SQL function that would do this.

I would recommend that you put the list of tables in Excel. Then put in a formula such as:

="select * from "&a1&" union all"

Copy this formula down. Voila! You almost have the view defniition.

Copy the column with these statements into the SQL command tool. Add the create view at the top. Remove the union all at the end. And voila. You can easily create the view.

Answer 3

Have a think about redefining your list of tables as a partitioned table, with a single master table and multiple child tables. http://www.postgresql.org/docs/9.2/static/ddl-partitioning.html

Alternatively, maintain a view to union all the tables together, and when you add a new table to the schema add it to the view also.