Hi René,

Did you see my email to the JDBC mailing list earlier today, in response
to someone question regarding finding out RI information from the
PostgreSQL system catalogs?

>From that email, the SQL needed to find out the RI triggers is :

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;

The returned "Trigger Arguments" column is a bytea field, and you'll
need to post-process this in the driver and extract the correct field
names (shouldn't be too hard).

The only other thing you'll probably need to do, apart from the
necessary JDBC driver related stuff, is do something with the "Trigger
Function" column.  It shows what the action is, and what the causing
event is.  i.e. "noaction_upd" shows a trigger for taking no action on
updates, "check_ins" shows a trigger which checks inserts (for UNIQUE
columns I think), etc.

Hope this is what you want.


Regards and best wishes,

Justin Clift

Rene Pijlman wrote:
> On Sat, 01 Sep 2001 21:26:52 +1000, Justin Clift wrote:
> >Does anyone know if we're planning on adding the Java
> >class/function/method/whatever called getExportedKeys?
> ...
> >In discussing this with Roger (the Product Manager of Innovative-IT who
> >has created this program), he says  our JDBC driver gives a
> >Driver.notImplemented() exception when trying to find out the RI info.
> >(Email history detailed below).
> He's right. The DatabaseMetaData.getExportedKeys() method is
> simply not yet implemented, both in the jdb1 and the jdbc2
> driver. Its open for development.
> If someone can write the query on the system catalogs I'll be
> glad to implement the Java code and a test case. The
> documentation of the info we need is available on
> http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html
> (look for getExportedKeys).
> Regards,
> René Pijlman <[EMAIL PROTECTED]>

"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?


Reply via email to