Re: querying system catalogs to extract foreign keys

Or if you download WebPG (phpPgAdmin) it includes code for retrieving
foreign keys.

Chris

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Justin Clift
> Sent: Sunday, 16 September 2001 1:04 PM
> To: Rene Pijlman
> Cc: jiby george; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] querying system catalogs to extract foreign keys
>
>
> Hi,
>
> In addition to this, Joel Burton's paper regarding Hacking the
> Referential Integrity tables gives very good insight into how to find
> out exactly what you're looking for, and the final example of SQL code
> at the end of the article will work as is :
>
> http://techdocs.postgresql.org/techdocs/hackingreferentialintegrity.php
>
> Modified code to show what you want :
>
> SELECT c.relname as "Trigger Table",
> substr(f.proname, 9) as "Trigger Function",
> t.tgconstrname as "Constraint Name",
> c2.relname as "Constraint Table",
> t.tgdeferrable as "Deferrable?",
> t.tginitdeferred as "Initially Deferred?",
> t.tgargs as "Trigger Arguments"
> FROM pg_trigger t,
> pg_class c,
> pg_class c2,
> pg_proc f
> WHERE t.tgrelid=c.oid
> AND t.tgconstrrelid=c2.oid
> AND tgfoid=f.oid
> AND t.tgenabled = 't'
> AND tgname ~ '^RI_'
> ORDER BY t.oid;
>
> Note the "Trigger Arguments" (bytea) column is where you look to find
> out the fields involved in the RI trigger.
>
> :-)
>
> Regards and best wishes,
>
> Justin Clift
>
>
> Rene Pijlman wrote:
> >
> > On 13 Sep 2001 22:56:16 -0700, you wrote:
> > >I tried to use the getImportedKeys and getExportedKeys of
> > >java.sql.DatabaseMetadata... But it didnt give any expected
> > >results...
> >
> > This is probably a limitation or bug in the JDBC driver. Please
> > post details of your problem on pgsql-jdbc@postgresql.org. E.g.
> > what results did you get, and what did you not get?
> >
> > >So can anyone tell me how to query the system
> > >catalogs to extract this info??
> >
> > The system catalogs are documented on
> > http://www.postgresql.org/idocs/index.php?catalogs.html
> >
> > Regards,
> > René Pijlman <rene@lab.applinet.nl>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
>
> --
> "My grandfather once told me that there are two kinds of people: those
> who work and those who take the credit. He told me to try to be in the
> first group; there was less competition there."
>      - Indira Gandhi
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>