Tom,

many thanks. Perfect advice as usual...

Corrected version attached for the archives.

Kuba

Tom Lane napsal(a):
Kuba Ouhrabka <[EMAIL PROTECTED]> 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
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';
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to