Re: Identify an inherited table

Bo Lorentsen <bl(at)netgroup(dot)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