Thread: Identify an inherited table

Hi ...

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;

id  | unknown
----------
1   | base
2   | child

Or something like this.

Is this possible or does this include alot funny SQL to gain ?

/BL

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