Re: SQL to retrieve foreign keys

Actually, right now it isn't trivial with the implementation.
You need to decode things out of the arguments to triggers which
are in a bytea and there aren't alot of reasonable sql level stuff
to decode it. If you don't mind doing some of the work on the front end,
you should be able do it.

If you do select tgconstrname, proname, tgargs, tgdeferrable,
tginitdeferred, tgnargs from pg_trigger,pg_proc where tgname like
'RI_ConstraintTrigger%' and tgfoid=pg_proc.oid;

That will get you three rows per foreign key constraint.
tgconstrname is the constraint name given to the constraint (currently, if
none is given, <unknown> is used).
proname tells you about what the constraint does. One will be
RI_FKey_check_ins which just checks new values in the fk table.
The other two are more interesting, one will be something like
RI_FKey_<something>_del, and the other RI_FKey_<something>_upd.
These tell the defined referential actions (no actions, cascade, etc)
for delete and update.
tgdeferrable says whether or not the constraint is DEFERRABLE
tginitdeferred says whether or not the constraint is INITIALLY DEFERRED
tgnargs holds the number of arguments to the trigger
tgargs holds the arguments in a bytea separated by \000
The arguments are as follows:
constraint name
fk table
pk table
match type
fk col1
pk col1
...
fk coln
pk coln

Stephan Szabo
sszabo(at)bigpanda(dot)com

On Wed, 11 Oct 2000, Colleen Williams wrote:

> Hi,
>
> I would like to write some SQL to extend the phpPgAdmin tool where it only
> displays primary keys related to a table. I have looked at the programmers
> doco and searched the discussions on foreign keys but was not able to
> decipher how to write the SQL. It is probably really trivial for someone
> who knows how the PostgreSQL catalog tables are related. Can someone please
> help me. Many Thanks.
>
> Colleen.
>