View Single Post

   
  #1 (permalink)  
Old 04-17-2008, 08:25 PM
Donald Fraser
 
Posts: n/a
Default Constraint Triggers request

Do you think in a later version you can show non-system constraint triggers that have been created by the user via:
CREATE CONSTRAINT TRIGGER ?

The current query pgAdmin uses to get trigger function information is:

SELECT t.oid, t.*, relname, nspname, des.description
FROM pg_trigger t
JOIN pg_class cl ON cl.oid=tgrelid
JOIN pg_namespace na ON na.oid=relnamespace
LEFT OUTER JOIN pg_description des ON des.objoid=t.oid
WHERE NOT tgisconstraint
AND tgrelid = xxxx:id
ORDER BY tgname

where xxxx is the oid of the table.

You could easily get the same result, which would include user defined constraint triggers by using:

SELECT t.oid, t.*, relname, nspname, des.description
FROM pg_trigger t
JOIN pg_class cl ON cl.oid=tgrelid
JOIN pg_namespace na ON na.oid=relnamespace
LEFT OUTER JOIN pg_description des ON des.objoid=t.oid
WHERE tgconstrrelid = 0:id
AND tgrelid = xxxx:id
ORDER BY tgname

Obviously you may want to change the reverse engineered SQL accordingly for constraint triggers.

Cheers
Donald Fraser
Reply With Quote