To: Kuba Ouhrabka <kuba@comgate.cz>
Cc: Merlin Moncure <merlin.moncure@rcsonline.com>,
	pgsql-performance@postgresql.org
Subject: Re: cached plans in plpgsql 
In-reply-to: <4357C4E8.6010602@comgate.cz> 
References: <6EE64EF3AB31D5448D0007DD34EEB3417DD640@Herge.rcsinc.local>
	<4357C4E8.6010602@comgate.cz>
Comments: In-reply-to Kuba Ouhrabka <kuba@comgate.cz>
	message dated "Thu, 20 Oct 2005 18:25:12 +0200"
Date: Thu, 20 Oct 2005 12:50:54 -0400
Message-ID: <11634.1129827054@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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