Thread: inherited type

Hi,

I am working with inheritance in postgresql 7.4. If I create a table named A,
and another named B that inherits from A, if I perform a query such a
"SELECT * FROM A" (without ONLY clause),
how can I get the type (identifier from pg_type) of the returned records??

Thanks in advance.

--

Miguel Angel Tribaldos Hervas <mitriher@teleco.upv.es> writes:
> I am working with inheritance in postgresql 7.4. If I create a table named A,
> and another named B that inherits from A, if I perform a query such a
> "SELECT * FROM A" (without ONLY clause),
> how can I get the type (identifier from pg_type) of the returned records??

I think what you are after is the "tableoid" system column.  Try
    SELECT tableoid, * FROM A;
or more readably
    SELECT tableoid::regclass, * FROM A;
or you can do the above "by hand"
    SELECT p.relname, A.* FROM A, pg_class p WHERE p.oid = a.tableoid;

If you really want the composite type IDs then it's
    SELECT p.reltype, A.* FROM A, pg_class p WHERE p.oid = a.tableoid;

            regards, tom lane

On Fri, Apr 01, 2005 at 12:42:19PM +0200, Miguel Angel Tribaldos Hervas wrote:
>
> I am working with inheritance in postgresql 7.4. If I create a table named A,
> and another named B that inherits from A, if I perform a query such a
> "SELECT * FROM A" (without ONLY clause),
> how can I get the type (identifier from pg_type) of the returned records??

You can look at the tableoid system column to get the oid from
pg_class:

  CREATE TABLE parent (pid integer);
  CREATE TABLE child (cid integer) INHERITS (parent);

  INSERT INTO parent (pid) VALUES (1);
  INSERT INTO child (pid, cid) VALUES (2, 3);

  SELECT tableoid, tableoid::regclass, * FROM parent;
   tableoid | tableoid | pid
  ----------+----------+-----
      39455 | parent   |   1
      39457 | child    |   2
  (2 rows)

If necessary, you could join tableoid against pg_type.typrelid
or pg_class.oid:

  SELECT t.typname, p.*
  FROM parent AS p
  JOIN pg_type AS t ON t.typrelid = p.tableoid;
   typname | pid
  ---------+-----
   parent  |   1
   child   |   2
  (2 rows)

  SELECT c.relname, p.*
  FROM parent AS p
  JOIN pg_class AS c ON c.oid = p.tableoid;
   relname | pid
  ---------+-----
   parent  |   1
   child   |   2
  (2 rows)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/