CODEWITHSUNDEEP
sqlpostgresql
John
John

Reputation: 1852

How to find where function is being used

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?

Upvotes: 2

Views: 3253

Answers (2)

Derick Hoang
Derick Hoang

Reputation: 1

select
    object_name(m.object_id) [object_name], m.definition
from
    sys.sql_modules m
where
    m.definition like N'%func1%'

Upvotes: -1

Nick Barnes
Nick Barnes

Reputation: 21346

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.

Upvotes: 2

Related Questions