Re: how to view table foreign keys/triggers?

Karin Nila Huegele <forcecommander(at)lorecrafters(dot)com> writes:
> I was wondering if you could show me how I could find out which columns
> in a table are constrained by foreign keys?

At the moment the only way is to look at the arguments passed to the
trigger. For example:

test72=# create table foo (f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
CREATE
test72=# create table bar (f2 int references foo);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
test72=# select * from pg_trigger where tgrelid = (select oid from pg_class where relname = 'bar');
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs
---------+-----------------------------+--------+--------+-----------+----------------+--------------+---------------+--------------+----------------+---------+--------+--------------------------------------------------------
811991 | RI_ConstraintTrigger_811993 | 1644 | 21 | t | t | <unnamed> | 811988 | f | f | 6 | | <unnamed>\000bar\000foo\000UNSPECIFIED\000f2\000f1\000
(1 row)

Between the nulls (\000) we see the constraint name, the referencing
table name, the referenced table name, the match type, the referencing
field name, and the referenced field name. IIRC, for a multi-column key
the last two fields are repeated N times.

In 7.3 it'll be a lot easier: the new pg_constraint table will record
the interesting info about foreign-key constraints. The same example
yields:

test=# select * from pg_constraint where conrelid = (select oid from pg_class where relname = 'bar');
conrelid | conname | contype | condeferrable | condeferred | confrelid | confupdtype | confdeltype | confmatchtype | conkey | confkey | conbin | consrc
----------+---------+---------+---------------+-------------+-----------+-------------+-------------+---------------+--------+---------+--------+--------
756831 | $1 | f | f | f | 756828 | a | a | u | {1} | {1} | |
(1 row)

Here the conkey and confkey columns are arrays of column numbers.

regards, tom lane