Re: Identify an inherited table

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bo Lorentsen <bl(at)netgroup(dot)dk>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Identify an inherited table
Date: 2001-08-24 13:34:40
Message-ID: 2924.998660080@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Wieger Uffink 2001-08-24 14:33:41 Error Codes, JDBC, SQLExceptions
Previous Message Phillip J. Allen 2001-08-24 11:42:27 How to start Postmaster during boot?