CODEWITHSUNDEEP
postgresql
suraj.tripathi
suraj.tripathi

Reputation: 467

Find the usage of a Postgres procedure

I want to change a Postgres procedure, but before doing this I want to make sure that it does not break anything.

To do this, I need to find its usage.

Upvotes: 1

Views: 607

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246588

If you have track_functions set to pl or all, you can use the view pg_stat_user_functions to see how often a function was called.

But that's probably not what you want; I assume you want to know from which other functions a function is called. This is much more difficult, because PostgreSQL does not track dependencies between a function and the objects used in its code. The best you can do is perform a string search:

SELECT oid::regprocedure
FROM pg_catalog.pg_proc
WHERE prosrc ILIKE '%your_function_name%';

That can of course produce false positives, particularly if the function name you search for is a string commonly used in code.

There can potentially be false negatives as well, consider this piece of PL/pgSQL code:

EXECUTE 'SELECT your_func' || 'tion_name()';

But you may be able to exclude such cases if you know your code base.

A completely different matter is the question where in your application code the function is used. But since you tagged the question only as , I assume that this is out of scope here.

Upvotes: 1

Related Questions