#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    56
    Rep Power
    12

    Question Foreign Keys - How do I show the details?


    Am I missing a nifty command here or is it downright difficult to find out details on foreign keys in postgres? I know they're there as the table has the constraint triggers, cryptically named so I have no idea which is which...

    Thanks in advance for any pointers!
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    56
    Rep Power
    12
    Foreign keys still cryptic - a patch for this is in the works. I can get the needed data by querying the system tables.

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

IMN logo majestic logo threadwatch logo seochat tools logo