Обсуждение: Change stored procedures schema name

Поиск
Список
Период
Сортировка

Change stored procedures schema name

От
Mainor Alonso Morales González
Дата:
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


--
Вложения

Re: Change stored procedures schema name

От
Alvaro Herrera
Дата:
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

От
Lennin Caro
Дата:

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




Вложения