Date: Wed, 14 Dec 2005 10:53:07 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Jost Degenhardt <jostdegenhardt@web.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Finding out to which table a specific row belongs
Message-ID: <20051214175306.GA82006@winnie.fuhr.org>
References: <43A055BF.1030907@web.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <43A055BF.1030907@web.de>

On Wed, Dec 14, 2005 at 06:26:23PM +0100, Jost Degenhardt wrote:
> I have the following problem: My database consists of several tables 
> that are inherited from each other with one single supertable on top of 
> that hierarchy. Now I would like to select a single row in that 
> supertable and want to find out to which of the tables in the hierarchy 
> it belongs.

http://www.postgresql.org/docs/8.1/interactive/ddl-inherit.html

  In some cases you may wish to know which table a particular row
  originated from.  There is a system column called tableoid in
  each table which can tell you the originating table:

http://www.postgresql.org/docs/8.1/interactive/ddl-system-columns.html

  tableoid

    The OID of the table containing this row. This column is particularly
    handy for queries that select from inheritance hierarchies (see
    Section 5.8), since without it, it's difficult to tell which
    individual table a row came from. The tableoid can be joined
    against the oid column of pg_class to obtain the table name.

Here's an example; instead of getting the table name via a join
with pg_class it uses a cast to regclass:

CREATE TABLE parent (t text);
CREATE TABLE child1 () INHERITS (parent);
CREATE TABLE child2 () INHERITS (parent);

INSERT INTO child1 VALUES ('one');
INSERT INTO child2 VALUES ('two');

SELECT tableoid::regclass, * FROM parent;
 tableoid |  t  
----------+-----
 child1   | one
 child2   | two
(2 rows)

-- 
Michael Fuhr