pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFinding out to which table a specific row belongs
2+ messages / 2 participants
[nested] [flat]
* Finding out to which table a specific row belongs
@ 2005-12-14 17:26 Jost Degenhardt <jostdegenhardt@web.de>
2005-12-14 17:53 ` Re: Finding out to which table a specific row belongs Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 2+ messages in thread
From: Jost Degenhardt @ 2005-12-14 17:26 UTC (permalink / raw)
To: pgsql-sql
Hi there,
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. As far as I understand the pg_depend table only shows the
hierarchy of the tables but does not contain the oids of the actual
rows. Is there any possibility to solve this problem by using the pg_*
tables?
I would very much appreciate any help!
Thank you in advance, Jost
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Finding out to which table a specific row belongs
2005-12-14 17:26 Finding out to which table a specific row belongs Jost Degenhardt <jostdegenhardt@web.de>
@ 2005-12-14 17:53 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Fuhr @ 2005-12-14 17:53 UTC (permalink / raw)
To: Jost Degenhardt <jostdegenhardt@web.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-12-14 17:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-14 17:26 Finding out to which table a specific row belongs Jost Degenhardt <jostdegenhardt@web.de>
2005-12-14 17:53 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox