I have a function named func1. It's used somewhere in the database but I don't know where.
I wrote this query to help me find where it's being used:
select proname,prosrc from pg_proc where prosrc like '%func1%'; How can I modify this query to check if triggers use func1 as well?
Assuming you know it's a trigger function (i.e. RETURNS TRIGGER), this should do it:
SELECT tgname, tgrelid::regclass FROM pg_trigger WHERE tgfoid = 'func1'::regproc If func1 is overloaded, you would need to use e.g. tgfoid = 'func1(text,text)'::regprocedure.
But in general, it might also appear in pg_aggregate, or pg_cast, or in a view definition, or a check constraint, or a dozen other places, and you don't want to have to check them all.
You can get to the bottom of this via pg_depend, which tracks all object dependencies in the database. For example:
SELECT classid::regclass FROM pg_depend WHERE refobjid = 'func1'::regproc If this returns e.g. pg_attrdef, then you know it's used in a column default. The other fields in pg_depend will tell you exactly which table/column it is. Note that a call from another function is not considered to be a dependency, so you still need to check pg_proc.prosrc.
But there's a simpler way to track down the majority of dependencies:
BEGIN; DROP FUNCTION func1(); ROLLBACK; If func1 is being used, the DROP will (probably) fail, and the error will tell you exactly where.
Even easier, if you've got a shell handy: Just to run pg_dump --schema-only and see where func1 turns up.
pg_trigger. The name of the function is replaced with the internal identifier of the function, and stored in pg_trigger.tgfoid.select object_name(m.object_id) [object_name], m.definition from sys.sql_modules m where m.definition like N'%func1%'
LIKEis case-sensitive. You probably wantILIKEinstead.