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(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