Search public functions

From PostgreSQL wiki
Jump to: navigation, search

Library Snippets

Search user-created functions

Works with PostgreSQL


Written in


Depends on


Search all functions in your PostgreSQL db for any matching terms.

The input will be used as the regular expresson in the regexp_matches() function.

Eg. to find 7 - 10 consecutive digits in any function:

select function_name,matching_terms from search_public_functions('[0-9]{7,10}',true);

string example at the bottom of page

-- load into db as superuser
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
        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;
        /* v_search_strings is a list, pipe-separated, exactly what we want to search against.
           NOTE: works on postgresql v8.4
           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;
$body$ language plpgsql SECURITY DEFINER;

It can be called like so:

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

original code (and any errors) by bricklen