Re: Referential cascade technique

On Mon, 23 Jul 2001, Jim Buttafuoco wrote:

> I use the following PLPERL/select "code" to view all FK's in my database
> .  I guess the "select" could be made into a pg_fkeys view.  What do
> people think...

The following was posted to the list a while ago, compliments of Michael
Fork:

   SELECT pt.tgargs, pt.tgnargs, pt.tgdeferrable, pt.tginitdeferred,
       pg_proc.proname, pg_proc_1.proname FROM pg_class pc,
       pg_proc pg_proc, pg_proc pg_proc_1, pg_trigger pg_trigger,
       pg_trigger pg_trigger_1, pg_proc pp, pg_trigger pt
   WHERE  pt.tgrelid = pc.oid AND pp.oid = pt.tgfoid
       AND pg_trigger.tgconstrrelid = pc.oid
       AND pg_proc.oid = pg_trigger.tgfoid
       AND pg_trigger_1.tgfoid = pg_proc_1.oid
       AND pg_trigger_1.tgconstrrelid = pc.oid
       AND ((pc.relname= '<< TABLENAME >>>')
       AND (pp.proname LIKE '%%ins')
       AND (pg_proc.proname LIKE '%%upd')
       AND (pg_proc_1.proname LIKE '%%del')
       AND (pg_trigger.tgrelid=pt.tgconstrrelid)
       AND (pg_trigger_1.tgrelid = pt.tgconstrrelid));

This will show all foreign keys on a table.


> Just a note.  I used PLPERL because the fkey data is stored in a BYTEA
> data field and other then a "C" function PLPERL works fine for me...

[snip]

--
Dominic J. Eidson
                                        "Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/              http://www.the-infinite.org/~dominic/