From: Kuba Ouhrabka <kuba@comgate.cz>
To: Merlin Moncure <merlin.moncure@rcsonline.com>
Subject: Re: cached plans in plpgsql
Date: Thu, 20 Oct 2005 18:25:12 +0200
Message-ID: <4357C4E8.6010602@comgate.cz> (raw)
In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3417DD640@Herge.rcsinc.local>
References: <6EE64EF3AB31D5448D0007DD34EEB3417DD640@Herge.rcsinc.local>
> [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
Attachments:recompile.sql (text/plain, 1.9K) download
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';
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kuba@comgate.cz, merlin.moncure@rcsonline.com
Subject: Re: cached plans in plpgsql
In-Reply-To: <4357C4E8.6010602@comgate.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox