4

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.

2 Answers 2

3

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%';
3
  • 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, 2015 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, 2015 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, 2015 at 9:51
2

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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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