Search 'grep-alike' through PostgreSql functions

ChristopheD picture ChristopheD · Dec 15, 2010 · Viewed 9.2k times · Source

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?

Answer

Frank Heikens picture Frank Heikens · Dec 15, 2010
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.