Skip site navigation (1) Skip section navigation (2)

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: (view raw or flat)
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);
regression=# insert into parent values (1);
INSERT 925961 1
regression=# create table child (f2 int) inherits(parent);
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group