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.Theother two are more interesting, one will be something likeRI_FKey_<something>_del, and the other
RI_FKey_<something>_upd.Thesetell 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 \000The arguments are as follows: constraint name fk table pk table
matchtype fk col1 pk col1 ... fk coln pk coln
Stephan Szabo
sszabo@bigpanda.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.
>