PostgreSQL: union of several tables using dynamic names

Question

I have a set of tables (about 100) in schema named qgep and which names start with vl_. They have all the same columns (colA, colB, colC).

What I'd like to do is to get one big table which is the union of all my vl_* tables, with also a column with the name of the original table.

I could get the list of the tables:

SELECT table_name
  FROM information_schema.tables 
  WHERE table_schema = 'qgep' 
  AND table_name LIKE 'vl_%'

The only way I found to solve my problem is to generate a SQL command to execute it further:

SELECT
  string_agg(
    'SELECT '''
    ||table_name
    ||''' AS table_name, colA, colB, colC FROM qgep.'
    ||table_name
  , ' UNION ')::text 
FROM information_schema.tables 
  WHERE table_schema = 'qgep' 
  AND table_name LIKE 'vl_%'"

Then executing this SQL command will output what I want. Although, it is very not performant, and quite ugly...

I would like to avoid using EXECUTE. Do you have any advice what to look for? Is there something I could do using WITH ... UNION ALL?

Would inheritance help me? Is it possible to know from which class is the record in the select?

They have all the same columns (colA, colB, colC). Add a column col_X to all of them, all with a different value, and combine the tables into one big table. — joop
– joop, Commented Mar 20, 2015 at 10:59
I am using this in a script. Anyway, I was hoping to find a nicer way. And maybe learn a bit. — Denis Rouzaud
– Denis Rouzaud, Commented Mar 20, 2015 at 12:47
each table lists possible options for value list (selection combobox), and options are listed in several languages. I would like to generate one big table to use as a dicitonary. — Denis Rouzaud
– Denis Rouzaud, Commented Mar 20, 2015 at 13:07

Vao Tsun · Accepted Answer · 2015-03-20 14:59:10Z

5

create or replace function uall() returns table ( code integer ,
  value character varying(50),
  active boolean,tablename text ) AS $$ 
declare
  _i int;
  _r record;
  _t text := '';
begin 
  select distinct string_agg($s$select *,'$s$||table_name||$s$' from $s$||table_name,' union all ') into _t from information_schema.tables where table_name like 'vl_%';
return query execute _t;
end;$$ language plpgsql
;

select * from uall()

answered Mar 20, 2015 at 14:59

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Denis Rouzaud Over a year ago

Thanks a lot for your help. I wanted to avoid using execute. I finally found the solution using inheritance.

Denis Rouzaud · Accepted Answer · 2015-03-23 06:31:59Z

3

The solution was indeed to use inheritance, and I finally found the solution on the Postgres doc.

SELECT p.relname, vl.* 
FROM qgep.is_value_list_base vl, pg_class p 
WHERE vl.tableoid = p.oid;

answered Mar 23, 2015 at 6:31

Denis Rouzaud

2,7083 gold badges31 silver badges48 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: union of several tables using dynamic names

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related