Thread: Change stored procedures schema name

Change stored procedures schema name

From

Mainor Alonso Morales González

Date:

06 August 2009, 18:09:54

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

Attachment

mmorales.jpg

Re: Change stored procedures schema name

From

Alvaro Herrera

Date:

06 August 2009, 18:25:55

Mainor Alonso Morales González wrote:
> There exist any way to change the name of my stored procedures
> schemas but the code used inside of them???

No.  You could try doing an UPDATE to pg_proc with a query that changed
the old schema for the new.

> sorry about my bad english :S

Perhaps you could use the pgsql-es-ayuda list, in any case.  (Your
english does not seem all that bad anyway).

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Change stored procedures schema name

From

Lennin Caro

Date:

06 August 2009, 19:06:14

From: Mainor Alonso Morales González <mmorales@ncq.co.cr>
Subject: [ADMIN] Change stored procedures schema name
To: pgsql-admin@postgresql.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

Attachment

mmorales.jpg