SEO

#1 (**permalink**) 04-08-2008, 08:22 PM

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.

--

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

#2 (**permalink**) 04-08-2008, 08:22 PM

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#3 (**permalink**) 04-08-2008, 08:22 PM

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/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

inherited type