From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kuba Ouhrabka <kuba@comgate.cz>
Cc: Merlin Moncure <merlin.moncure@rcsonline.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: cached plans in plpgsql
Date: Thu, 20 Oct 2005 12:50:54 -0400
Message-ID: <11634.1129827054@sss.pgh.pa.us> (raw)
In-Reply-To: <4357C4E8.6010602@comgate.cz>
References: <6EE64EF3AB31D5448D0007DD34EEB3417DD640@Herge.rcsinc.local>
<4357C4E8.6010602@comgate.cz>
Kuba Ouhrabka <kuba@comgate.cz> writes:
> 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;
This will not work at all; it makes far too many incorrect assumptions,
like proargnames always being non-null and having subscripts that match
proargtypes. (It'll mess things up completely for anything that has OUT
arguments, too.)
It's pretty much the hard way to form a function reference anyway ---
you can just cast the function OID to regprocedure, which aside from
avoiding a lot of subtle assumptions about the catalog contents,
will deal with schema naming issues, something the above likewise
fails at.
To avoid having to reconstruct argument names/types, I'd suggest using
an ALTER FUNCTION command instead of CREATE OR REPLACE FUNCTION, maybe
DECLARE fullproname text := a_oid::regprocedure;
...
EXECUTE 'ALTER FUNCTION ' || fullproname || ' RENAME TO ' || Var_datos.proname;
regards, tom lane
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: tgl@sss.pgh.pa.us, kuba@comgate.cz, merlin.moncure@rcsonline.com
Subject: Re: cached plans in plpgsql
In-Reply-To: <11634.1129827054@sss.pgh.pa.us>
* 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