Classes of returned rows
Hi all,
Is there a way of determining the original class of a row when querying
inherited tables? Consider:
CREATE TABLE users (
uid int4 SERIAL PRIMARY KEY,
email varchar(60)
);
CREATE TABLE clients (
surname varchar(30)
[etc]
) INHERITS (users);
CREATE TABLE suppliers (
surname varchar(30)
[etc]
) INHERITS (users);
Then, when I execute "SELECT * FROM users", I'd like to know which classes
each row belongs to, ie. suppliers, clients or users. Hopefully, as easy as
"SELECT oid, * FROM users" is!
I'm using 7.1 already, and it kicks arse. Many thanks to pgsql-hackers for a
wonderful piece of software I use every day. Thanks. :)
- Jeff
--
"Can we have a special TELSABUG category, and everything gets
dropped to fix them first?" - Telsa Gwynne
Jeff Waugh <jdub@aphid.net> writes:
Is there a way of determining the original class of a row when querying
inherited tables? Consider:
Use the "tableoid" pseudo-column.
regards, tom lane
<quote who="Tom Lane">
Use the "tableoid" pseudo-column.
Ah! Awesome.
A little playing with pg_class, and we have table/class names too.
PostgreSQL has cool new things to find every day...
Thanks Tom!
- Jeff
--
Web development with PHP is like injecting pure rust with a
high-pressure hose. For pain relief.
Jeff Waugh writes:
Is there a way of determining the original class of a row when querying
inherited tables?
Use the implicit tableoid column.
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
On Thu, Apr 26, 2001 at 05:39:43PM +0200, Peter Eisentraut wrote:
Jeff Waugh writes:
Is there a way of determining the original class of a row when querying
inherited tables?Use the implicit tableoid column.
okay, all i get from
select c.relname,a.attname
from pg_class c,pg_attribute a
where a.attrelid=c.oid
AND a.attname LIKE 'table%'
order by attname,relname;
is
relname | attname
------------+------------
pg_indexes | tablename
pg_rules | tablename
pg_tables | tablename
pg_tables | tableowner
cmax, xmin and oid show up, but no tableoid.
is it a 7.1 thing? i'm using 7.0.3...
--
and anybody care to enlighten a greenhorn as to
where xmax/xmin and cmax/cmin come in useful?
--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html
will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
<quote who="will trillich">
select c.relname,a.attname
from pg_class c,pg_attribute a
where a.attrelid=c.oid
AND a.attname LIKE 'table%'
order by attname,relname;
select pg_class.relname, *
from users
where pg_class.relfilenode = tableoid;
relname | login | passhash
-----------+----------+----------
users | user | user
users | user2 | user2
clients | client | client
clients | client2 | client2
suppliers | supplier | supplier
Results from 7.1 :)
- Jeff
--
Web development with PHP is like injecting pure rust with a
high-pressure hose. For pain relief.