Re: cached plans in plpgsql

Tom,

many thanks. Perfect advice as usual...

Corrected version attached for the archives.

Kuba

Tom Lane napsal(a):
> Kuba Ouhrabka <kuba(at)comgate(dot)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