Re: Change stored procedures schema name

From: Mainor Alonso Morales González <mmorales(at)ncq(dot)co(dot)cr>
Subject: [ADMIN] Change stored procedures schema name
To: pgsql-admin(at)postgresql(dot)org
Date: Thursday, August 6, 2009, 8:23 PM

There exist any way to change the name of my stored procedures schemas
but the code used inside of them???

for example:

I have this stored procedure:

CREATE OR REPLACE FUNCTION schema1.example(double precision) RETURNS
numeric AS

$BODY$

DECLARE

    pIn    ALIAS FOR $1;   

BEGIN

    insert into schema1.table values(pIn,0); --this is just an example
:)

    return 0;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE

If I change the schema name using PGADMIN it just change this part:

    CREATE OR REPLACE FUNCTION new_schema.example(double precision)
RETURNS numeric AS

    ...........

I'm asking about any way to change the schema of the command inside of
the code automatically, because I have many

stored procedure in the database (about 63) and this process could take
so much time, I hope had been clear, sorry about

my bad english :S

--

i have this script

UPDATE pg_proc
 SET prosrc=(
  SELECT
   replace(prosrc, 'TRUE','FALSE') from pg_proc a
  WHERE 
   pg_proc.oid = a.oid
  )
  WHERE
   pg_proc."pronamespace"=(SELECT oid from pg_namespace where nspname = 'public')
   AND pg_proc.prosrc like ('%RETURN%TRUE%')

*******************

Change ('%RETURN%TRUE%') to the text to search

change replace(prosrc, 'TRUE','FALSE') to the text to change