Обсуждение: querying system catalogs to extract foreign keys
I wanted to extract foriegn keys from the postgresql database related to each of the tables.. I tried to use the getImportedKeys and getExportedKeys of java.sql.DatabaseMetadata... But it didnt give any expected results... So can anyone tell me how to query the system catalogs to extract this info?? Thanx Jiby
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>
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
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 >