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

13 Sep 2001 22:56:16 -0700
jiby@intelesoftech.com (jiby george)
querying system catalogs to extract foreign keys
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>

Fri, 14 Sep 2001 20:38:01 +0200
Rene Pijlman <rene@lab.applinet.nl>
Re: 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

Sun, 16 Sep 2001 15:03:34 +1000
Justin Clift <justin@postgresql.org>
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
>


Mon, 17 Sep 2001 10:11:53 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: querying system catalogs to extract foreign keys