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>

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