Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199402090602.WAA26397@nobozo.CS.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
Message-Id: <199402090602.WAA26385@nobozo.CS.Berkeley.EDU>
From: aoki@postgres.Berkeley.EDU (Paul M. Aoki)
To: SRIRAMK@charlie.usd.edu
Cc: postgres@postgres.Berkeley.EDU
Subject: Re: How to see list of db and classes created? 
Reply-To: aoki@postgres.Berkeley.EDU (Paul M. Aoki)
In-reply-to: Your message of Tue, 8 Feb 1994 22:12:57 -0600 (CST) 
	     <940208221257.dc9e@charlie.usd.edu> 
Date: Tue, 08 Feb 1994 22:02:51 -0800
Resent-To: postgres-dist@postgres.Berkeley.EDU
Resent-Date: Tue, 08 Feb 94 22:02:52 -0800
Resent-XMts: smtp

SRIRAMK@charlie.usd.edu writes:
> In Postgres, is there a way to list the databases created by me, and 
> the classes and attr names and types contained in each of the databases
> created? The manual does not seem to talk abt such a facility or am I 
> missing something simple?

an extract from the (recently revised) user manual:

.sh 2 "How to Find Out What's in a Database"
.lp
This section explains how you can answer questions such as,
.q "What tables do I have in this database?"
and
.q "What extensions have been added to my database?"
.\"------------------------------------
.sh 3 "About the \*(PP System Catalogs"
.lp
If you are familiar with standard relational systems, you know that
they store information about databases, tables, etc., in what are
commonly known as
.i "system catalogs" .
(Some systems call this the
.i "data dictionary" ).
The catalogs appear to the user as tables, like any other, but the
DBMS stores its internal bookkeeping in them.  One key difference
between \*(PP and standard relational systems is that \*(PP stores
much more information in its catalogs \(em not only information about
tables and attributes, but also information about the types,
procedures, access methods, and so on.  These tables can be modified
by the user, and since \*(PP bases its internal operation on these
tables, this means that \*(PP can be extended by users.  By
comparison, standard relational systems can only be extended by
changing hard-coded procedures within the DBMS.
.lp
The following classes contain information that may be useful to the
end user.  There are many other system catalogs, but there should
never be a reason to query them.
.TS
center tab(|);
cf(C)|c
lf(C)|l.
catalog name|description
=
pg_database|databases
pg_class|class
pg_attribute|attributes
pg_index|indexes

pg_proc|procedures
pg_type|types
pg_operator|operators
pg_aggregate|aggregates

pg_am|access methods
pg_amop|access method operators
pg_amproc|access method support functions
pg_opclass|access method operator classes

pg_user|users
pg_group|user groups
.TE
.\"------------------------------------
.sh 3 "Querying the System Catalogs"
.lp
Before executing any of the queries below, be sure to execute the \*(PP
.cW vacuum
command.  (The queries will run much more quickly that way.)
.lp
This query prints the names of all database adminstrators and the name
of their database(s).
.(C
* retrieve (user_name = u.usename,
            database = d.datname)
      from u in pg_user,
           d in pg_database
      where u.usesysid = int2in(int4out(d.datdba))
      sort by user_name, database
 \\g
.)C
The following query lists all user-defined classes in the database.
.(C
* retrieve (class_name = c.relname)
      from c in pg_class
      where c.relkind = 'r'     /* exclude indexes */
        and c.relname !~ "^pg_"  /* exclude catalogs */
      sort by class_name
 \\g
.)C
The following query prints a report of the user-defined attributes and
their types for all user-defined classes in the database.
.(C
* retrieve (class_name = c.relname, 
            attr_name = a.attname, 
            attr_type = t.typname)
      from c in pg_class,
           a in pg_attribute,
           t in pg_type
      where c.relkind = 'r'    /* no indexes */
        and c.relname !~ "^pg_" /* no catalogs */
        and a.attnum > 0       /* no system att's */
        and a.attrelid = c.oid
        and a.atttypid = t.oid
      sort by class_name, attr_name
 \\g
.)C
This query lists all left-associative (post-fix) operators.
.(C
* retrieve (left_assoc = o.oprname,
            operand = right.typname,
            return_type = result.typname)
      from o in pg_operator,
           right in pg_type,
           result in pg_type
      where o.oprkind = 'l'
        and o.oprright = right.oid
        and o.oprresult = result.oid
      sort by operand
 \\g
.)C
This query lists all right-associative (pre-fix) operators.
.(C
* retrieve (right_assoc = o.oprname,
            operand = left.typname,
            return_type = result.typname)
      from o in pg_operator,
           left in pg_type,
           result in pg_type
      where o.oprkind = 'r'
        and o.oprleft = left.oid
        and o.oprresult = result.oid
      sort by operand
 \\g
.)C
This query lists all binary operators.
.(C
* retrieve (binary_op = o.oprname,
            left_opr = left.typname,
            right_opr = right.typname, 
            return_type = result.typname)
      from o in pg_operator,
           left in pg_type,
           right in pg_type,
           result in pg_type
      where o.oprkind = 'b'
        and o.oprleft = left.oid
        and o.oprright = right.oid
        and o.oprresult = result.oid
      sort by left_opr, right_opr
 \\g
.)C
This query returns the name, number of arguments (parameters) and
return type of all user-defined C functions.  The same query can be
used to find all built-in C functions if you change the
.q C
to 
.q internal ,
or all \*(PQ functions if you change the
.q C
to 
.q postquel .
.(C
* retrieve (p.proname, 
            arguments = p.pronargs,
            returntype = t.typname)
      from p in pg_proc,
           l in pg_language,
           t in pg_type
      where p.prolang = l.oid
        and p.prorettype = t.oid
        and l.lanname = "C"
      sort by proname
 \\g
.)C
This query lists all of the aggregate functions that have been
installed, except for 
.cW count
(which can take any type as its argument).
.(C
* retrieve (a.aggname, t.typname)
      from a in pg_aggregate,
           t in pg_type
      where a.aggbasetype = t.oid
      sort by aggname
 \\g
.)C
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  aoki@postgres.Berkeley.EDU
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki