From: | Lennin Caro <lennin(dot)caro(at)yahoo(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org, Mainor Alonso Morales González <mmorales(at)ncq(dot)co(dot)cr> |
Subject: | Re: Change stored procedures schema name |
Date: | 2009-08-06 22:05:59 |
Message-ID: | 334197.1582.qm@web59514.mail.ac4.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Beena J P | 2009-08-07 05:11:46 | Re: CSV Utility |
Previous Message | Steve Crawford | 2009-08-06 21:40:16 | Re: CSV Utility |