Re: Identify an inherited table

Bo Lorentsen <bl@netgroup.dk> writes:
> When using a database with inherited tables it somtimes would be nice if
> the row you are looking at is a base, a child or even a grandchild type.
> Like this :
>     SELECT id, type() FROM base;

What you want is the standard system column tableoid:

regression=# create table parent (f1 int);
CREATE
regression=# insert into parent values (1);
INSERT 925961 1
regression=# create table child (f2 int) inherits(parent);
CREATE
regression=# insert into child values (11,22);
INSERT 925964 1
regression=# select tableoid,* from parent;
 tableoid | f1
----------+----
   925959 |  1
   925962 | 11
(2 rows)

You can join against pg_class to convert the table oid to table name, eg

regression=# select c.relname, p.* from parent p, pg_class c where
regression-# c.oid = p.tableoid;
 relname | f1
---------+----
 parent  |  1
 child   | 11
(2 rows)

            regards, tom lane