Subject: gleaning the database structure

From: Stewart Allen <[email-masked]>
Date: Thu, 4 Aug 1994 17:57:57 -0400 (EDT)

Is there a simple set of queries which would provide basic table/column
 structure information within a database? I've poured over all the .ps
 documents that came with 4.0x and the pg_<whatever> structures listed
 there do not exactly match those used by the system.

 In Informix this information is simply defined in systables and 
 syscolumns.

 I've also encountered problems when searching on the oid. Actually,
 I've been completely unable to do it. Whether using the built in
 operators or just "table.oid = xxx" it fails with a error about
 explicit casts and defining operators for oid and int4 (aren't the
 built-in operators supposed to do this?).

 I guess one basic question arises from this:
	- where do I get better information about oid's, casts, etc?

 The FAQ is only minimally useful and I've read all of the available
 documentation several times (or maybe I haven't, but I've tried to
 be diligent in my research).

--
 Stewart Allen
 ([email-masked])

==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "[email-masked]"

   If this fails, send mail to "[email-masked]" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================

---

From: [email-masked] (Paul M. Aoki)
Date: Thu, 04 Aug 94 16:07:22 -0700

>  there do not exactly match those used by the system.

look in the documentation for 4.2.  (unfortunately the ftp server
is down, probably until the end of next week, since we are still in 
the process of moving.)
--
  Paul M. Aoki          |  University of California at Berkeley
  [email-masked]  |  Dept. of EECS, Computer Science Division (#1776) 
                        |  Berkeley, CA 94720-1776

---

From: Mark Costlow <[email-masked]>
Date: Thu, 4 Aug 1994 18:20:53 -0600

On Thu, 4 Aug 1994 17:57:57 -0400 (EDT),
Stewart Allen <[email-masked]> said:

Stewart>  Is there a simple set of queries which would provide basic
Stewart>  table/column structure information within a database? 

Here are a few queries that I worked up to do this.  I built these by
looking at the code for pg_relshow (in the contributed software that comes
with Postgres).  

------------------------------------

To get the list of databases:

retrieve (pg_database.datname)

------------------------------------

Get the list of tables in the current database:

retrieve (pg_class.relname) where pg_class.relname !~ "^pg_"

------------------------------------

Get the list of attributes for a table ("mailees" in this example):

retrieve (FieldNum = a.attnum, FieldName = a.attname, Type = t.typname)
    from c in pg_class, a in pg_attribute, t in pg_type
    where c.relname = "mailees"
      and c.oid = a.attrelid
      and a.attnum > 0
      and t.oid = a.atttypid
    sort by FieldNum using <

------------------------------------

>>Chx
--
Mark Costlow
Southwest Cyberport - Public Access Internet in Albuquerque, NM
Email: [email-masked]   Voice: 505-271-0009

---

From: Thilo Gaul <[email-masked]>
Date: Fri, 5 Aug 1994 12:18:06 +0200

> structure information within a database? I've poured over all the .ps
pg_class, pg_type, pg_attribute? 
You might need to use Version 4.2, users manual page 27 explains all you
wanted, I think.

> explicit casts and defining operators for oid and int4 
table.oid? Defining a attribute with a system-class name won't work!
If this should only express the usage of an "oid":
table.attr = "123"::oid  will do it...

>	- where do I get better information about oid's, casts, etc?
the only reason is, that a number is passed as "int4" and there's no
implicit typecast for "oid"s.

Thilo.

([email-masked])