PostgreSQL:: Find functions containing

Sometimes when you need to find a function which contains specific text in the definition or description, it's handy to have a function that makes that possible.

Here is a function that will allow you to do just that:

 /**  
 DEFINE  
  */  
 CREATE OR REPLACE FUNCTION my_admin_schema.sel_function_containing(_schema_name text, _search_value text)  
  RETURNS TABLE (  
   schema text, sproc_name text, arg_names text, return_type text, description text, definition text  
  ) AS  
  $BODY$  
 BEGIN  
  RETURN QUERY   
   WITH funcs AS (  
     SELECT  
       n.nspname::text AS schema  
      ,proname::text AS sproc_name  
      ,proargnames::text AS arg_names  
      ,t.typname::text AS return_type  
      ,d.description  
      ,pg_get_functiondef(p.oid) as definition  
     FROM pg_proc p  
      JOIN pg_type t on p.prorettype = t.oid  
      JOIN pg_description d on p.oid = d.objoid  
      JOIN pg_namespace n on n.oid = p.pronamespace  
     WHERE n.nspname = _schema_name  
   )  
   SELECT *  
   FROM funcs  
   WHERE funcs.definition ~* _search_value  
     OR funcs.description ~* _search_value  
   ;  
 END; $BODY$ LANGUAGE plpgsql STABLE COST 100  
 ;;  
 /**  
 EXAMPLE USAGE  
  */  
 SELECT * FROM offerpoint_admin1.sel_function_containing('my_app_schema', 'status')  
 ;;