From: | "Christopher Kings-Lynne" <chriskl(at)familyhealth(dot)com(dot)au> |
---|---|
To: | "Justin Clift" <justin(at)postgresql(dot)org>, "Rene Pijlman" <rene(at)lab(dot)applinet(dot)nl> |
Cc: | "jiby george" <jiby(at)intelesoftech(dot)com>, <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: querying system catalogs to extract foreign keys |
Date: | 2001-09-17 02:11:53 |
Message-ID: | ECEHIKNFIMMECLEBJFIGMEOMCBAA.chriskl@familyhealth.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Or if you download WebPG (phpPgAdmin) it includes code for retrieving
foreign keys.
Chris
> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org]On Behalf Of Justin Clift
> Sent: Sunday, 16 September 2001 1:04 PM
> To: Rene Pijlman
> Cc: jiby george; pgsql-hackers(at)postgresql(dot)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(at)postgresql(dot)org(dot) 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(at)lab(dot)applinet(dot)nl>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-09-17 02:40:54 | Re: 7.3 |
Previous Message | Christopher Kings-Lynne | 2001-09-17 01:57:42 | 7.3 |