Re: Getting Primary & Foreign Key Information? - Mailing list pgsql-general

Here are copies of two responses I received recently when I posted a similar
question.

------- reply from Dominic J. Eidson ----------------------------
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.

-- reply from-----------------------------------------
--  "Jim Buttafuoco"<jim@spectrumtelecorp.com> ---------------

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...


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...


Let me know if it works for you..
Jim


--
-- I called this function "j" during development and never changed
-- it.
--
CREATE FUNCTION j(bytea,varchar) RETURNS text AS '


@data = split(/\\\\000/, $_[0]);

$a = $data[0] if $_[1] eq "FKNAME";
$a = $data[1] if $_[1] eq "FTAB";
$a = $data[2] if $_[1] eq "TTAB";
$a = join(",",(@data)[4,6,8,10,12,14]) if $_[1] eq "FCOLS";
$a = join(",",(@data)[5,7,9,11,13,15]) if $_[1] eq "TCOLS";

$a =~ s/,+$//g;
return $a;
' LANGUAGE 'plperl';



select  a.tgconstrname,
        j(tgargs,'FTAB'::varchar) || '(' || j(tgargs,'FCOLS'::varchar)
|| ')' as from,

        j(tgargs,'TTAB'::varchar) || '(' || j(tgargs,'TCOLS'::varchar)
|| ')' as references

,
        cd as "cascade_delete",
        cu as cascade_update
from ( pg_trigger a left join
(select tgconstrname,'Y' as cd from pg_trigger where tgfoid = 1646) b on
(a.tgconstrname =
b.tgconstrname) )
left join
(select tgconstrname,'Y' as cu from pg_trigger where tgfoid = 1647) c on
(a.tgconstrname =
b.tgconstrname)
where
        tgfoid = 1644
        and
        tgisconstraint;




Hope this helps you.
Mike

===================
Mike Finn
Tactical Executive Systems
mike.finn@tacticalExecutive.com