Thread: Extracting user db tabel info from system tables???
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
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).