Search 'grep-alike' through PostgreSql functions

Question

When refactoring PostgreSql functions (and more specific: while searching for 'unused' functions) it would be handy to have a function available to search for a specific string within the function definitions.

Does anyone know if this is the best approach (trying to 'grep'-search the function definitions) or are there alternative methods available?

How would one implement this functionality?

score 30 · Accepted Answer · answered Dec 15 '10 at 10:42

30

SELECT 
  proname AS functionname, 
  prosrc AS source 
FROM 
  pg_proc 
WHERE 
  prosrc ~* 'your_string';

Details about how to use a regex in PostgreSQL, can be found in the manual.

answered Dec 15 '10 at 10:42

Frank Heikens

117,544
24
142
135

+1 (did not realize it could be this simple ;-) I'll wait a little while to see if other approaches pop up, but this is working perfectly fine! – ChristopheD Dec 15 '10 at 10:54

score 5 · Answer 2 · answered Jan 11 '17 at 08:00

Frank's answer is great. Use this enhanced version to additionally list the schema which helps to locate the function:

SELECT
  proname AS functionname,
  prosrc AS source,
  nspname AS schema
FROM
  pg_proc
INNER JOIN pg_namespace ON (pg_proc.pronamespace = pg_namespace.oid)
WHERE
  prosrc ~* 'your_string';

Search 'grep-alike' through PostgreSql functions

2 Answers2