Thread: 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.