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?

Answer 1

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()