Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199401162010.MAA29430@nobozo.CS.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
Date: Sun, 16 Jan 94 12:09:58 PST
From: jean@gso.SAIC.COM (Jean Anderson)
Message-Id: <9401162009.AA27834@ratatosk>
To: postgres@postgres.Berkeley.EDU
Subject: Re: attribute names of a class
Resent-To: postgres-dist@postgres.Berkeley.EDU
Resent-Date: Sun, 16 Jan 94 12:10:04 -0800
Resent-XMts: smtp

I've been collecting 4.1 system catalog queries.  The file down below
has some sample queries that can be \i(ncluded) in a monitor session. It
assumes the demo database from $POSTGRESHOME/src/regress/demo is loaded.

Feel free to send me corrections, enhancements, favorite additions, etc.
I'll incorporate and repost.

 -jean
  jean@gso.saic.com

-----------------------------< clip, clip, clip >-----------------------------

/* >>>>>>>>>>>>>>>>> POSTGRES 4.1 System Catalog Queries <<<<<<<<<<<<<<<<<  */

/* ========================= CLASSES =====================================  */
/* List user-created classes.                                               */

retrieve (p.relname) 
from     p in pg_class 
where    p.relkind="r"               /* "r" gets classes, "i" gets indices  */
and      p.relname !~ "pg_*"         /* system catalogs start with pg_      */
\g


/* ========================= ATTRIBUTES =================================== */
/* List user attributes for "student".                                      */

retrieve (r.relname, a.attnum, a.attname, t.typname)
from    a in pg_attribute, t in pg_type, r in pg_class
where   a.attrelid = r.oid
and     a.atttypid = t.oid
and     r.relname ="student"
and     a.attnum > 0                /* system attributes have attnum=0 */
sort by attnum
\g


/* ========================= INDICES ====================================== */
/* Output indexed class, index name, and the first attribute of the index.  */
/*                                                                          */
/* r1.relname   index name                                                  */
/* r2.relname   class the index is on                                       */
/* indexrelid   = pg_class.oid where pg_class.relkind="i"                   */
/* indrelid     = pg_class.oid where pg_class.relkind="r"                   */
/* indkey       indkey[1-7] = pg_attribute.attnum                           */

retrieve (class_name=r2.relname, index_name=r1.relname, a.attname)
from    r1 in pg_class, r2 in pg_class, i in pg_index, a in pg_attribute
where   r1.oid=i.indexrelid
and     r2.oid = i.indrelid
and     r1.relkind = "i"
and     a.attrelid=i.indrelid
and     a.attnum=i.indkey[1]
sort by class_name
\g


/* ========================= INHERITANCE  ================================= */
/* List classes that inherit another class.                                 */

retrieve (This_Class=r1.relname, Inherits=r2.relname, Sequence=p.inhseqno)
from      p in pg_inherits, r2 in pg_class, r1 in pg_class
where     p.inhrel=r1.oid
  and     p.inhparent=r2.oid
\g


/* List the attributes in "student" that are inherited from another class. */

retrieve (r.relname, a1.attname, a1.attnum, inherited = "yes")
from    a1 in pg_attribute, a2 in pg_attribute, p in pg_inherits, r in pg_class
where   r.relname="student"
  and   a1.attrelid = r.oid
  and   a1.attname = a2.attname
  and   (a2.attrelid = p.inhparent and a1.attrelid = p.inhrel)
  and   a1.attnum > 0
sort by attnum
\g


/* ========================= FUNCTIONS  =================================== */
/* proname      Function name.                                              */
/* prolang      Language. 13 = c, 14 = postquel; anybody know what 11 is?   */
/* typname      Return type.                                                */
/* proargtypes  Array of argument types, joins to pg_type.oid.              */
/* prosrc       The Postquel query if language is postquel.                 */
/* probin       The object code file if language is C.                      */

/* Output C functions */

retrieve (C_Function=p.proname, return_type=t.typname, p.proargtypes, p.probin) 
from      p in pg_proc, t in pg_type
where     p.prorettype=t.oid
and       p.prolang = 13::oid
\g


/* Output postquel functions, including the type of the first argument */

retrieve (PQ_Function=p.proname, return_type=t.typname, 
          arg1_type=t2.typname, p.prosrc)
from      p in pg_proc, t in pg_type, t2 in pg_type
where     p.prorettype=t.oid
and       p.proargtypes[1] = t2.oid
and       p.prolang = 14::oid
\g

/* ========================= AGGREGATES =================================== */

retrieve (a.aggname, input = t1.typname, output = t2.typname)
from    a in pg_aggregate, t1 in pg_type, t2 in pg_type
where   t1.oid = a.aggtranstype
and     t2.oid = a.aggfinaltype
\g