Message-ID: <435899BD.3070808@comgate.cz>
Date: Fri, 21 Oct 2005 09:33:17 +0200
From: Kuba Ouhrabka <kuba@comgate.cz>
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Subject: Re: cached plans in plpgsql
References: <6EE64EF3AB31D5448D0007DD34EEB3417DD640@Herge.rcsinc.local>
	<4357C4E8.6010602@comgate.cz> <11634.1129827054@sss.pgh.pa.us>
In-Reply-To: <11634.1129827054@sss.pgh.pa.us>
Content-Type: multipart/mixed; boundary="------------030704090600010405000005"

This is a multi-part message in MIME format.
--------------030704090600010405000005
Content-Type: text/plain; charset=windows-1250; format=flowed
Content-Transfer-Encoding: 7bit

Tom,

many thanks. Perfect advice as usual...

Corrected version attached for the archives.

Kuba

Tom Lane napsal(a):
> 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

--------------030704090600010405000005
Content-Type: text/plain;
 name="recompile.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="recompile.sql"

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';

CREATE OR REPLACE FUNCTION recompile_function(a_oid oid) RETURNS INTEGER AS $func$
DECLARE
  lv_name TEXT;
  lv_fullname TEXT;
BEGIN

  SELECT INTO lv_name proname FROM pg_proc 
  WHERE 
  		oid = a_oid
  ;

  lv_fullname := a_oid::regprocedure;
  
  EXECUTE 'ALTER FUNCTION ' || lv_fullname || ' RENAME TO ugly_function_name';
  lv_fullname := a_oid::regprocedure;
  EXECUTE 'ALTER FUNCTION ' || lv_fullname || ' RENAME TO ' || lv_name;

  RETURN 0;

END;
$func$ LANGUAGE 'plpgsql';

--------------030704090600010405000005--