Re: cached plans in plpgsql
От | Kuba Ouhrabka |
---|---|
Тема | Re: cached plans in plpgsql |
Дата | |
Msg-id | 4357C4E8.6010602@comgate.cz обсуждение исходный текст |
Ответ на | Re: cached plans in plpgsql ("Merlin Moncure" <merlin.moncure@rcsonline.com>) |
Ответы |
Re: cached plans in plpgsql
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-performance |
> [howto recreate plpgsql functions] > > Start here: > http://archives.postgresql.org/pgsql-hackers/2005-09/msg00690.php Great, thanks! I slighltly modified the function - it was not working for overloaded functions (same name, different arguments) and for functions with named arguments. Modified version attached for anyone interested - not perfect but works for me... Kuba CREATE OR REPLACE FUNCTION recompile_function(a_oid oid) RETURNS INTEGER AS $func$ DECLARE Par_proc TEXT; Var_datos RECORD; Var_codigo text; Var_args varchar; Var_nameArg varchar; Var_nameRet varchar; i int; BEGIN SELECT proretset, prorettype, proargtypes, proargnames, prosrc, pronargs, proname INTO Var_datos FROM pg_proc WHERE oid = a_oid FOR UPDATE ; Par_proc := Var_datos.proname; SELECT typname::varchar INTO Var_nameRet FROM pg_type WHERE oid = Var_datos.prorettype; Var_codigo := 'CREATE OR REPLACE FUNCTION '||Par_proc||'('; IF Var_datos.pronargs > 0 THEN Var_args := ''; FOR i IN 0..Var_datos.pronargs-1 LOOP SELECT typname::varchar INTO Var_nameArg FROM pg_type WHERE oid = Var_datos.proargtypes[i]; Var_args := Var_args|| COALESCE(Var_datos.proargnames[i+1], '') || ' ' || Var_nameArg||', '; END LOOP; Var_codigo := Var_codigo||RTRIM(Var_args,', '); END IF; if Var_datos.proretset THEN Var_codigo := Var_codigo||') RETURNS SETOF '||Var_nameRet||' AS'''; ELSE Var_codigo := Var_codigo||') RETURNS '||Var_nameRet||' AS'''; END IF; Var_codigo := Var_codigo|| replace(Var_datos.prosrc,'''' , '\''''); Var_codigo := Var_codigo||'''LANGUAGE ''plpgsql'''; EXECUTE(Var_codigo); RETURN 0; END; $func$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION recompile_all_functions() RETURNS INTEGER AS $func$ DECLARE lr_rec RECORD; li_x INTEGER; BEGIN FOR lr_rec IN SELECT p.oid as oid FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace LEFT JOIN pg_language l ON l.oid = p.prolang WHERE NOT p.proisagg AND pg_catalog.pg_function_is_visible(p.oid) AND n.nspname != 'pg_catalog' AND NOT p.proname IN ('recompile_all_functions', 'recompile_function') AND l.lanname = 'plpgsql' LOOP li_x := recompile_function(lr_rec.oid); END LOOP; RETURN 0; END; $func$ LANGUAGE 'plpgsql';
В списке pgsql-performance по дате отправления: