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?

Zeiler

Answer 1

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.

Enrapture

+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! – Zeiler

Answer 2

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';

Copro