Обсуждение: PLPGSQL function to search function source for a list of terms
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
On 17/09/2010 17:37, bricklen wrote: > 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. That could be pretty useful - why don't you put it on the wiki? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On Fri, Sep 17, 2010 at 9:57 AM, Raymond O'Donnell <rod@iol.ie> wrote: > That could be pretty useful - why don't you put it on the wiki? > > Ray. > I was going to put an entry at http://wiki.postgresql.org/wiki/Category:Library_Snippets, but I couldn't find the "edit" option. Maybe I'm blind? I just noticed how poorly formatted that cut 'n paste job turned out too. Ugh.
On 17/09/2010 18:12, bricklen wrote: > On Fri, Sep 17, 2010 at 9:57 AM, Raymond O'Donnell<rod@iol.ie> wrote: >> That could be pretty useful - why don't you put it on the wiki? >> >> Ray. >> > I was going to put an entry at > http://wiki.postgresql.org/wiki/Category:Library_Snippets, but I > couldn't find the "edit" option. Maybe I'm blind? I just noticed how > poorly formatted that cut 'n paste job turned out too. Ugh. You need to be logged in to edit the wiki - if you haven't yet got a community account, you can get one on the pg.org site: http://www.postgresql.org/community/signup Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On Fri, Sep 17, 2010 at 10:16 AM, Raymond O'Donnell <rod@iol.ie> wrote: > On 17/09/2010 18:12, bricklen wrote: >> >> On Fri, Sep 17, 2010 at 9:57 AM, Raymond O'Donnell<rod@iol.ie> wrote: >>> >>> That could be pretty useful - why don't you put it on the wiki? >>> >>> Ray. >>> >> I was going to put an entry at >> http://wiki.postgresql.org/wiki/Category:Library_Snippets, but I >> couldn't find the "edit" option. Maybe I'm blind? I just noticed how >> poorly formatted that cut 'n paste job turned out too. Ugh. > > You need to be logged in to edit the wiki - if you haven't yet got a > community account, you can get one on the pg.org site: > > http://www.postgresql.org/community/signup > > Ray. > > -- > Raymond O'Donnell :: Galway :: Ireland > rod@iol.ie > Aha! Thanks, I'll do that.
On Fri, Sep 17, 2010 at 10:17 AM, bricklen <bricklen@gmail.com> wrote: > On Fri, Sep 17, 2010 at 10:16 AM, Raymond O'Donnell <rod@iol.ie> wrote: >> On 17/09/2010 18:12, bricklen wrote: >>> >>> On Fri, Sep 17, 2010 at 9:57 AM, Raymond O'Donnell<rod@iol.ie> wrote: >>>> >>>> That could be pretty useful - why don't you put it on the wiki? >>>> >>>> Ray. >>>> >>> I was going to put an entry at >>> http://wiki.postgresql.org/wiki/Category:Library_Snippets, but I >>> couldn't find the "edit" option. Maybe I'm blind? I just noticed how >>> poorly formatted that cut 'n paste job turned out too. Ugh. >> >> You need to be logged in to edit the wiki - if you haven't yet got a >> community account, you can get one on the pg.org site: >> >> http://www.postgresql.org/community/signup >> >> Ray. >> >> -- >> Raymond O'Donnell :: Galway :: Ireland >> rod@iol.ie >> > > Aha! Thanks, I'll do that. > It has now been added to http://wiki.postgresql.org/wiki/Category:Library_Snippets