Simplify complex union all in PostgreSQL

Question

I have very complex query in PostgreSQL that unions several tables, that all have common set of fields we want to union. currently we're pregenerating this query. I have seen solution to this using UNPIVOT and I'm wondering if it's possible to do this in PostgreSQL flavour of SQL.

What I have is something like

SELECT a,b,c FROM a UNION ALL
SELECT a,b,c FROM c UNION ALL
SELECT a,b,c FROM d UNION ALL
SELECT a,b,c FROM e UNION ALL
SELECT a,b,c FROM f

I'd like to have names of tables to union in separate table and use that for this query.

PS. Changing schema is not an option.

Answer 1

Use inheritance for the Postgres documentation for inheritance. You'll need to recreate the database, but that's easy if you dump the tables without schema, create a new schema with inheritance, and load the data back.

The schema would look something like this:

CREATE TABLE base (a, b, c);
CREATE TABLE a () INHERITS (base);
CREATE TABLE b () INHERITS (base);
....

With this design, you can do a simple select:

SELECT * FROM base;

This will return all rows in base and all the tables inheriting from base.

Read about PostgreSQL table partitioning from the docs if you haven't done so already.

Answer 2

If you really can't fix your design (or don't want to use the very good suggestion from jmz), your only choice is probably a set returning function that builds the necessary UNION "on the fly" and then returns the results from that.

create or replace function my_union()
 returns table(a integer, b integer, c integer)
as
$body$
declare
  union_cursor refcursor;
  table_cursor cursor for SELECT table_name FROM union_source; 
  union_query text := '';
begin

  -- build the query string for the union
  for table_list_record in table_cursor loop
    if union_query  '' then 
      union_query := union_query||' UNION ALL';
    end if;
    union_query := union_query||' SELECT a,b,c FROM '||table_list_record.table_name;
  end loop;

  -- run the union and return the result
  for a,b,c IN EXECUTE union_query LOOP
    return next;
  end loop; 
end;
$body$
language plpgsql;

The function builds the necessary UNION based on the table names in union_source and then executes the union and returns the result.

You could extend the union_source table to also store the column list for each table, if the columns do not always have the same names.

To use this function, simply select from it:

select *
from my_union()