Get all triggers that call a certain function

Question

So an insert violates a constraint because a trigger modifies the data when it shouldn't for this case.

Thing is, I already disabled the triggers that I thought were causing this, and the function still gets called.

How do I best get the triggers I'm missing?

Note that the function is called WITHIN the trigger function, so

SELECT * FROM information_schema.triggers WHERE action_statement LIKE '%my_function%';

returns nothing;

Answer 1

To find which trigger uses a certain trigger function, query the catalog:

SELECT tgname,
       tgrelid::regclass
FROM pg_trigger
WHERE tgfoid = 'my_trigger_function'::regproc;

If the function you are talking about is not the trigger function proper, but called from a trigger function, things are more difficult.

Since functions in languages other than c and internal are stored as strings, PostgreSQL doesn't track dependencies between functions.

So the best you can do in that case is a substring search:

SELECT oid::regprocedure
FROM pg_proc
WHERE prosrc ILIKE '%myfuncname%';

Answer 2

You may try this:

select routine_name, routine_schema
 from information_schema.routines 
 where routine_definition ~ 'the_evil_one'
 and data_type = 'trigger';

I hope that it will work for you provided that the trigger routine's languge is plpgsql.