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" character varying(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 | 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?
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..)
relname | usename | relacl | attname | typname | attlen |
attnotnull | relname | indisunique | indisprimary
---------+---------+-----------------+----------+---------+--------+--------
----+-----------+-------------+--------------
prey | mac | {"=r","mac=rw"} | yeartime | float8 | 8 | t
| prey_pkey | t | t
prey | mac | {"=r","mac=rw"} | pred | varchar | -1 | t
| prey_pkey | t | t
prey | mac | {"=r","mac=rw"} | pred_age | int8 | 8 | t
| prey_pkey | t | t
prey | mac | {"=r","mac=rw"} | prey | varchar | -1 | t
| prey_pkey | t | t
prey | mac | {"=r","mac=rw"} | prey_age | int8 | 8 | t
| prey_pkey | t | t
prey | 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
[EMAIL PROTECTED]
