From: Kuba Ouhrabka <kuba@comgate.cz>
Subject: Re: cached plans in plpgsql
Date: Thu, 20 Oct 2005 18:25:12 +0200
Organization: Hub.Org Networking Services
Lines: 110
Message-ID: <4357C4E8.6010602@comgate.cz>
References: <6EE64EF3AB31D5448D0007DD34EEB3417DD640@Herge.rcsinc.local>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="------------060809030609060305070302"
To: Merlin Moncure <merlin.moncure@rcsonline.com>
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3417DD640@Herge.rcsinc.local>
To: pgsql-performance@postgresql.org

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

 > [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

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

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

--------------060809030609060305070302--