PostgreSQL database with several hundred of stored functions, including obsolete, not used etc.


I need to find out all the stored functions that have any relationship to the table X - as I want to change the table structure. Some of them might be not used, so I can't do that just by looking through the code.

The solution I have ATM is running psql's \df+ and grepping output, but I'd prefer more database-like solution, i.e. by using information schema. This will definitely be a repetitive task and I'd like to have it nice and clean.

Any suggestions?


2 Answers 2


The body of a function is just stored as string. There is no list of referenced objects. (That's different from views, for instance, where actual links to referenced tables are saved.)

This query for Postgres 10 or older uses the system catalog information function pg_get_functiondef() to reconstruct the CREATE FUNCTION script for relevant functions and searches for the table name with a case-insensitive regular expression:

SELECT n.nspname AS schema_name
     , p.proname AS function_name
     , pg_get_function_arguments(p.oid) AS args
     , pg_get_functiondef(p.oid) AS func_def
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  NOT p.proisagg
AND    n.nspname NOT LIKE 'pg_%'
AND    n.nspname <> 'information_schema'
AND    pg_get_functiondef(p.oid) ~* '\mbig\M';

It should do the job, but it's obviously not bullet-proof. It can fail for dynamic SQL where the table name is generated dynamically and it can return any number of false positives - especially if the table name is a common word.

Aggregate functions and all functions from system schemas are excluded.

\m and \M mark the beginning and end of a word in the regular expression.

The system catalog pg_proc changed in Postgres 11. proisagg was replaced by prokind, true stored procedures were added. You need to adapt. Related:

  • 1
    Yep... it's not totally robust, in the sense that it won't find EXECUTE expressions like 'mm_'||name_parameter, and it won't cope correctly with quoted names like "my""table"" or with case-folding, but it'll do most of what most people will want. Jul 25, 2013 at 5:38
  • @CraigRinger: Yeah, dynamic queries with EXECUTE are almost impossible to cover. But case-folding can be covered with ~* instead of ~ - or any other case-insensitive pattern-matching. Jul 25, 2013 at 5:43
  • So long as the operator isn't crazy enough to actually create tables named "MyTable" and MyTable, at least... and honestly, that's a "well, that might be allowed but it isn't smart" move. Jul 25, 2013 at 6:12
  • Thanks for the answer! I actually don't use dynamic table name construction anywhere and all table names are lowercase. Jul 25, 2013 at 11:16

This query is pretty easy to use:

SELECT proname, proargnames, prosrc FROM pg_proc WHERE prosrc ILIKE '%Text to search%';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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