PLPGSQL function to search function source for a list of terms

[bricklen <bricklen@xxxxxxxxx>]

Here is a plpsql function I put together to search db functions in
schemas other than pg_catalog and information_schema. Not the greatest
of coding, but it might help someone else trying to solve the same
issue I was having: to search all public functions for a list of
terms. Sample usage is below the code, as are some sample results.

Any changes/improvements/critcisms appreciated.  (or even a better version!)

[works in pg version 8.4, not in 8.2 -- not sure about 8.3]

create or replace function search_public_functions(p_search_strings
TEXT, p_case_insensitive BOOLEAN, OUT function_name TEXT, OUT
matching_terms TEXT) RETURNS SETOF RECORD AS
$body$
declare
        x                       RECORD;
        qry                     TEXT;
        v_match                 BOOLEAN := 'false';
        v_matches               TEXT;
        v_search_strings        TEXT := p_search_strings;
        v_case_insensitive      BOOLEAN := p_case_insensitive;
        v_funcdef               TEXT;
begin
        /* v_search_strings is a list, pipe-separated, exactly what we
want to search against.
           NOTE: works on postgresql v8.4
           example:
           select function_name,matching_terms from
search_public_functions('crosstab|intersect|except|ctid',true);
        */

        if (v_case_insensitive IS NOT FALSE) then
                v_case_insensitive := TRUE;
        end if;

        qry :=  'SELECT n.nspname||''.''||p.proname||''
(''||pg_catalog.pg_get_function_arguments(p.oid)||'')''::TEXT as
funcname,
                        (select pg_catalog.pg_get_functiondef(p.oid))
as funcdef,
                        p.oid as funcoid
                FROM pg_catalog.pg_proc p
                     LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
p.pronamespace
                WHERE pg_catalog.pg_function_is_visible(p.oid)
                AND n.nspname <> ''pg_catalog''
                AND n.nspname <> ''information_schema''
                AND NOT p.proisagg
                ORDER BY 1';

        if (p_case_insensitive IS TRUE) then
                v_search_strings := LOWER(v_search_strings);
        end if;

        for x in execute qry loop
                v_match := 'false';
                function_name := null;
                v_funcdef := null;

                select into v_match x.funcdef ~* v_search_strings;

                if ( v_match IS TRUE ) then
                        v_matches := null;
                        v_funcdef := x.funcdef;
                        if (p_case_insensitive IS TRUE) then
                                v_funcdef := LOWER(v_funcdef);
                        end if;
                        select array_to_string(array_agg(val),',')
into v_matches from (select distinct
array_to_string(regexp_matches(v_funcdef, v_search_strings ,'g'),',')
as val) as y2;

                        function_name := x.funcname;
                        matching_terms := v_matches;
                        RETURN NEXT;
                end if;
        end loop;
end;
$body$ language plpgsql SECURITY DEFINER;

select function_name,matching_terms from
search_public_functions('crosstab|intersect|except|ctid',true);


               function_name                          | matching_terms
--------------------------------------------------------------+----------------
 public.array_intersect (anyarray, anyarray)   | intersect
 public.cant_delete_error ()                         | except
 public.crosstab2 (text)                               | crosstab
 public.crosstab3 (text)                               | crosstab
 public.crosstab4 (text)                               | crosstab
 public.crosstab (text)                                 | crosstab
 public.crosstab (text, integer)                     | crosstab
 public.crosstab (text, text)                          | crosstab
 public.find_bad_block (p_tablename text)    | ctid,except

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general