Re: Querying a parent table's child schemas

Greetings all,

I was wondering if there was a way to SELECT from a parent table
and display the schema associated with a child table's data. There's
the general idea now let me explain further... I have a parent table
called alarms, whenever a new user uploads any alarms to the database a
schema is created which inherits the attributes of the public alarms
table (the different schemas allow me to keep track of who uploaded
what). In this way, I am able to look through all the alarms by
querying public.alarms as well as query each individual schema's alarms
table. What I want to do however, is let's say perform a query like:

SELECT date, time, alarm_code, alarm_msg, child_schema() FROM
public.alarms;

I know there is a command current_schema(), but when querying the
public.alarms table I only get the public schema, and not the schema of
the associated children tables.

Does anybody know if this is even possible? If so any information or
tips would be greatly appreciated.

Thanks in advance,

James L. Matthews

"Matthews, James" <jmatthews(at)Railpower(dot)com> writes:
> I was wondering if there was a way to SELECT from a parent table
> and display the schema associated with a child table's data.

It's not entirely clear to me which meaning of "schema" you have in
mind, but perhaps the tableoid system column would help you? That
lets you determine which child table the row really came from,
and then you can join to the system catalogs to get whatever data
you are actually after.

regards, tom lane

In my database there is the public schema (default) with the parent
alarms table. Each 'customer' has their own schema via CREATE SCHEMA,
and then each schema has an alarms table (CREATE TABLE alarm INHERITS
public.alarms). While the tabloid column seems like it should work when
I do the following:

SELECT a.date, a.log_msg, p.relname
FROM alarms a, pg_class p
WHERE a.tableoid = p.oid;

The relname column just lists alarms for every row. I'm looking to find
the schema name, not the table name. Is it a little clearer now?
Regardless thank you for your reply as I believe that it is a step in
the right direction! Any further advice would be appreciated.

Respectfully,

James L. Matthews, III

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: Thursday, August 30, 2007 12:01 PM
To: Matthews, James
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Querying a parent table's child schemas

"Matthews, James" <jmatthews(at)Railpower(dot)com> writes:
> I was wondering if there was a way to SELECT from a parent table
> and display the schema associated with a child table's data.

It's not entirely clear to me which meaning of "schema" you have in
mind, but perhaps the tableoid system column would help you? That
lets you determine which child table the row really came from,
and then you can join to the system catalogs to get whatever data
you are actually after.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

"Matthews, James" <jmatthews(at)Railpower(dot)com> writes:
> I do the following:

> SELECT a.date, a.log_msg, p.relname
> FROM alarms a, pg_class p
> WHERE a.tableoid = p.oid;

> The relname column just lists alarms for every row.

If they're all named 'alarms', then yeah ... what you need is another
join to pg_namespace using relnamespace.

regards, tom lane