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

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;