for example i have a student table with (studentid) i want to get a list of triggers that use this field...

for example if in table X there is a trigger that uses studentid i want it to be shown in the query result.


You can do that by querying pg_proc and pg_trigger,

SELECT t.tgname FROM pg_trigger t, pg_proc p 
WHERE t.tgfoid=p.oid AND p.prosrc like '%studentid%';
|improve this answer|||||
  • mmm the name of the column is studentid is used in other places... for example in another table i have studentidfor. this will make querry not working. the solution needs to be based on column unique identifier. also studentid can be a field in another table regardless of this one. – Jason Jun 3 '15 at 9:13
  • So you can't do this, triggers called functions, functions are based on source code, there is no hard link based on ooid attribute for sample. – Rodolphe Jun 3 '15 at 9:17
  • but this is very problematic... if for example i have a column named "id" and i use this method i will get lots of results most of them are irrelevant. – Jason Jun 3 '15 at 9:51

If you want to filter on the tablle name if you have too many triggers just add a filter on relname from pg_class

SELECT tgrelid::regclass,t.tgname FROM pg_trigger t, pg_proc p, pg_class c 
WHERE c.oid = t.tgrelid AND t.tgfoid=p.oid 
AND c.relkind = 'r'
AND p.prosrc LIKE '%studentid%' AND c.relname = 'X';
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.