Subject: Inherited Attributes Question

From: Vivek Shivpuri <[email-masked]>
Date: Mon, 28 Mar 1994 18:22:48 -0500 (EST)

I have a relation "a" and relation "b" with the following syntax -

 create a(t=text)
 create b(k=int4) inherits (a)

 What is the query to find out which attribute belongs to which class?
 In other words I need the following information :-

 Class "a" has one attribute called "t"
 Class "b" has two attributes "k" and "t", but "t" is an inherited attribute.

 What should my query be to get the above information?

 I am having this problem because..pg_attribute class has an attribute called
 "attdefrel", which according to the catalog is of type RELATION...i.e. it
 gives the oid number of the relation that defines the attribute. Which is
 what I need, right? But pg_attribute.attdefrel is always = 0......see the
 monitor session below....

 Vivek
 ([email-masked])

--------------example monitor session---------------------------

* create a(t=text) \g

Query sent to backend is "create a(t=text) "
CREATE
Go 
* create b(k=int4) inherits (a) \g

Query sent to backend is "create b(k=int4) inherits (a) "
CREATE
Go 
* retrieve (pg_attribute.all) where pg_class.relname = "a"and pg_attribute.attrelid = pg_class.oid and pg_attribute.attnum > 0 \g

Query sent to backend is "retrieve (pg_attribute.all) where pg_class.relname = "a"and pg_attribute.attrelid = pg_class.oid and pg_attribute.attnum > 0 "
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| attrelid    | attname     | atttypid    | attdefrel   | attnvals    | atttyparg   | attlen      | attnum      | attbound    | attbyval    | attcanindex | attproc     | attnelems   | attcacheoff |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 47746       | t           | 25          | 0           | 0           | 0           | -1          | 1           | 0           | f           | f           | 0           | 0           | -1          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Go 
* retrieve (pg_attribute.all) where pg_class.relname = "b"and pg_attribute.attrelid = pg_class.oid and pg_attribute.attnum > 0 \g

Query sent to backend is "retrieve (pg_attribute.all) where pg_class.relname = "b"and pg_attribute.attrelid = pg_class.oid and pg_attribute.attnum > 0 "
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| attrelid    | attname     | atttypid    | attdefrel   | attnvals    | atttyparg   | attlen      | attnum      | attbound    | attbyval    | attcanindex | attproc     | attnelems   | attcacheoff |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 47804       | t           | 25          | 0           | 0           | 0           | -1          | 1           | 0           | f           | f           | 0           | 0           | -1          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 47804       | k           | 23          | 0           | 0           | 0           | 4           | 2           | 0           | t           | f           | 0           | 0           | -1          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Go 
* \q

---------------end monitor session-------------------------------

---

From: [email-masked] (Paul M. Aoki)
Date: Mon, 28 Mar 94 16:24:59 -0800

>  What is the query to find out which attribute belongs to which class?

the 4.2 reference manual contains a description of the system catalog 
attributes.

pg_inherits.inhrel (oid of child) and pg_inherits.inhparent (oid of 
parent) give you the edges in the inheritance hierarchy.  inhseqno
records the order that classes were specified in the "create" command 
(of interest in multiple inheritance cases).

offhand i can't think of a single-line query that, given only the 
classname of the child, tells you the "highest" parent that contains
a given attribute.  on the other hand, traversing the hierarchy 
yourself seems relatively straightforward.

if all you want is "is this attribute inherited from ANYBODY", then
the following query seems to work.  may not be the simplest possible
query (oh, the joys of normalization), but.. oh yeah, if you have
multiple inheritance you may want to make this "retrieve unique".

i don't think you're allowed to override an inherited attribute -- if
you can, this won't work.

Go 
* create a(t=text)
\g
Go
* create b(k=int4) inherits (a)
\g
Go
* retrieve (ca.attname)
from ca in pg_attribute, pa in pg_attribute, i in pg_inherits,
c in pg_class, p in pg_class
where
c.oid = i.inhrel and
p.oid = i.inhparent and
c.oid = ca.attrelid and
p.oid = pa.attrelid and
ca.attname = pa.attname and
ca.attnum > 0 and
c.relname = "b"
\g
---------------
| attname     |
---------------
| t           |
---------------
Go
*

--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki