Re: Extracting user db tabel info from system tables??? - Mailing list pgsql-sql

On Fri, 5 Jan 2001, Marc Cromme wrote:

> I have some problems on making the right joins on system tables to extract
> the 
> structure of some user defined tables/databases. I use PostgreSQL 7.0.2 on
> an 
> RedHat 7.0 box.
> 
> PROBLEM 1: I tried to make a Foreign key constraint from the primary key of 
> table 'pred' to the table 'prey'. The PRIMARY KEY ("yeartime", "pred",
> "pred_age") 
> of ' pred' should be a Foreign key in 'prey'. Hovever, when I make a dump I
> get this: 

Later on in the dump, there should be a line of the form:
CREATE CONSTRANT TRIGGER ... 
referencing the tables in question.  It'll probably be near the end.  We 
currently dump the fk constraints as their internal representation
(constraint triggers) rather than as the original constraints.

> PROBLEM 2:
> I try to make some queries on POSTGRES system tables to determine the table
> definitions 
> dynamically in a PHP script- the idea is that I do not want to toutch the
> PHP code in case 
> that the database table structure changes. I can retrieve the structure of
> the 'prey' table
> primary keys by the following SQL query:  
> 
> baltic=> SELECT a.attname, ic.relname, i.indisunique, i.indisprimary 
> FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a 
> WHERE bc.oid = i.indrelid and ic.oid = i.indexrelid and a.attrelid = bc.oid 
> and bc.relname = 'prey' and (i.indkey[0] = a.attnum or i.indkey[1] =
> a.attnum 
> or i.indkey[2] = a.attnum or i.indkey[3] = a.attnum or i.indkey[4] =
> a.attnum 
> or i.indkey[5] = a.attnum or i.indkey[6] = a.attnum or i.indkey[7] =
> a.attnum) 
> ORDER BY ic.relname, a.attname;
> 
>  attname  |  relname  | indisunique | indisprimary 
> ----------+-----------+-------------+--------------
>  pred     | prey_pkey | t           | t
>  pred_age | prey_pkey | t           | t
>  prey     | prey_pkey | t           | t
>  prey_age | prey_pkey | t           | t
>  yeartime | prey_pkey | t           | t
> (5 rows)
> 
> Question 2: How can I avoid the sequences of OR statements, which are
> errorprone (and unelegant) 
> in case that there are more than 7 fields in the primary key?

You could probably look at the array stuff in contrib for the 
element in array functions/operators and use that.

> PROBLEM 3:
> I can get a nice description of all the 'prey' table fields by issuing the
> following SQL query:
> 
> baltic=> SELECT c.relname,  u.usename, c.relacl, a.attname, t.typname,
> a.attlen, a.attnotnull 
> FROM pg_class c, pg_attribute a, pg_type t , pg_user u 
> WHERE u.usesysid = c.relowner AND c.relname = 'prey' AND a.attnum > 0 AND
> a.attrelid = c.oid 
> AND a.atttypid = t.oid 
> ORDER BY a.attnum;
> 
>  relname | usename |     relacl      | attname  | typname | attlen |
> attnotnull  
> ---------+---------+-----------------+----------+---------+--------+--------
> ---- 
>  prey    | mac     | {"=r","mac=rw"} | yeartime | float8  |      8 | t
>  prey    | mac     | {"=r","mac=rw"} | pred     | varchar |     -1 | t
>  prey    | mac     | {"=r","mac=rw"} | pred_age | int8    |      8 | t
>  prey    | mac     | {"=r","mac=rw"} | prey     | varchar |     -1 | t
>  prey    | mac     | {"=r","mac=rw"} | prey_age | int8    |      8 | t
>  prey    | mac     | {"=r","mac=rw"} | wstom    | float8  |      8 | f
>  prey    | mac     | {"=r","mac=rw"} | stomcon  | float8  |      8 | f
> (7 rows)
> 
> 
> QUESTION 3: How do I merge the two above queries to get a table like this
> (Outer Join ???? Union???
> I know how to emulate outer joints by an Union and Where ... Not In
> (select..), but I can't find out
> how to join two queries, and not two tables..)

Your best bet is probably to make views for the two queries and then do
the outer join using those in which case they effectively look like 
tables.  You could do it without the views, but that'll be kind of 
long and hard to read.

> QUESTION 4: How do I extract also information on foreign keys from the
> system tables, 
> and add two columns to the above table like the following?
> 
> fkey       | ftable
> -----------+---------
> pred_pkey  | pred
> pred_pkey  | pred
> pred_pkey  | pred
> NULL       | NULL
> NULL       | NULL
> NULL       | NULL
> NULL       | NULL

The foreign key constraint information is stored in pg_trigger.  The
problem is that there's no good way to get the column information from
within sql right now (they're stored as arguments in tgargs).