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

QUESTIONS ON USEFULL JOINS ON SYSTEM TABLES FOR USE WITH PHP-WEBINTERFACE

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: 


CREATE TABLE "pred" ("yeartime" float8 NOT NULL,"pred" character varying(10) NOT NULL,"pred_age" int8 NOT
NULL,"stomachn"float8,"totcon" float8,"consum" float8,PRIMARY KEY ("yeartime", "pred", "pred_age")
 
);
REVOKE ALL on "pred" from PUBLIC;
GRANT SELECT on "pred" to PUBLIC;
GRANT UPDATE,DELETE,SELECT on "pred" to "mac";


CREATE TABLE "prey" ("yeartime" float8 NOT NULL,"pred" character varying(10) NOT NULL,"pred_age" int8 NOT NULL,"prey"
charactervarying(10) NOT NULL,"prey_age" int8 NOT NULL,"wstom" float8,"stomcon" float8,PRIMARY KEY ("yeartime", "pred",
"pred_age","prey", "prey_age")
 
);
REVOKE ALL on "prey" from PUBLIC;
GRANT SELECT on "prey" to PUBLIC;
GRANT UPDATE,DELETE,SELECT on "prey" to "mac";

QUESTION 1): How to define Foreign keys properly????



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           | tpred_age | prey_pkey | t
   | tprey     | prey_pkey | t           | tprey_age | prey_pkey | t           | tyeartime | 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?



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 | tprey    | mac     | {"=r","mac=rw"} | pred
| varchar |     -1 | tprey    | mac     | {"=r","mac=rw"} | pred_age | int8    |      8 | tprey    | mac     |
{"=r","mac=rw"}| prey     | varchar |     -1 | tprey    | mac     | {"=r","mac=rw"} | prey_age | int8    |      8 |
tprey   | mac     | {"=r","mac=rw"} | wstom    | float8  |      8 | fprey    | 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..)
relname | usename |     relacl      | attname  | typname | attlen |
attnotnull |  relname  | indisunique | indisprimary
---------+---------+-----------------+----------+---------+--------+--------
----+-----------+-------------+-------------- prey    | mac     | {"=r","mac=rw"} | yeartime | float8  |      8 | t
| prey_pkey | t           | tprey    | mac     | {"=r","mac=rw"} | pred     | varchar |     -1 | t
| prey_pkey | t           | tprey    | mac     | {"=r","mac=rw"} | pred_age | int8    |      8 | t
| prey_pkey | t           | tprey    | mac     | {"=r","mac=rw"} | prey     | varchar |     -1 | t
| prey_pkey | t           | tprey    | mac     | {"=r","mac=rw"} | prey_age | int8    |      8 | t
| prey_pkey | t           | tprey    | mac     | {"=r","mac=rw"} | wstom    | float8  |      8 | f
| NULL`     | NULL        | NULL      prey    | mac     | {"=r","mac=rw"} | stomcon  | float8  |      8 | f
| NULL`     | NULL        | NULL      
(7 rows)


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



I do thank you very much in advance on any hints on how to juggle around
with PostgreSQL system tables.

Your's 

Marc Cromme
mac@dfu.min.dk