PostgreSQL inheritance: get the record class name

Question

I have a base table value_list with columns code, value, active. I have some tables vl_a, vl_b, vl_c, etc. inheriting from value_list.

Is there a way, when doing a SELECT * FROM base to know from which class the child comes from.

In other words, I would like to have:

code | value |  active | class
-----+-------+---------+--------
 1   |   c   |  true   | vl_a
 3   |   g   |  false  | vl_b
 5   |   d   |  true   | vl_a
 7   |   f   |  false  | vl_c
 2   |   u   |  false  | vl_c
 2   |   q   |  true   | vl_b
 8   |   a   |  false  | vl_a

Is this possible ?

For more details, here would be the tables:

CREATE TABLE value_list(
  code integer NOT NULL,
  value character varying(50),
  active boolean,
  CONSTRAINT pkey PRIMARY KEY (code)
)

CREATE TABLE vl_a() INHERITS (value_list);
CREATE TABLE vl_b() INHERITS (value_list);
CREATE TABLE vl_c() INHERITS (value_list);

Could you maybe add your database schema with table definitions to the question. It's not all clear how they're designed. — jpw, Mar 20 '15 at 12:47

Vao Tsun · Answer 1 · 2015-03-20T14:47:57.303

1

dictionary won't let you to, but you can union manually:

   select *,'vl_a' from vl_a
    union all
    select *,'vl_b' from vl_b
    union all
    select *,'vl_c' from vl_c

enter image description here

well, this gives it:

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

edited Mar 20 '15 at 14:47

answered Mar 20 '15 at 13:57

Vao Tsun

47,234
13
100
132

I have about 100 tables...I wanted to do this programmatically. Maybe using information_schema. – Denis Rouzaud Mar 20 '15 at 14:11
well you can create a function which returns setof or table or query and use execute dynamic sql for that... – Vao Tsun Mar 20 '15 at 14:23
@DenisRouzaud I updated to be dynamic from info schema – Vao Tsun Mar 20 '15 at 14:48
thanks for your help! I wanted to avoid using execute. The generated query is awfully long. There were a second page for inheritance on the postgres doc, and they give the solution in it. – Denis Rouzaud Mar 23 '15 at 06:30

score 1 · Accepted Answer · answered Mar 23 '15 at 06:28

1

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 '15 at 06:28

Denis Rouzaud

2,412
2
26
45

PostgreSQL inheritance: get the record class name

2 Answers2