Re: Changing a schema's name with function1 calling function2 - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Changing a schema's name with function1 calling function2 |
Date | |
Msg-id | aa3e044d-f688-4f91-b6c4-570c0cec96f1@aklaver.com Whole thread Raw |
In response to | Changing a schema's name with function1 calling function2 ("Wilma Wantren" <wilma.wantren@eclipso.de>) |
Responses |
Re: Re: Changing a schema's name with function1 calling function2
("Wilma Wantren" <wilma.wantren@eclipso.de>)
|
List | pgsql-general |
On 12/21/23 05:47, Wilma Wantren wrote: > Now as text mail, sorry. > I accidentally posted the following to the bugs mailing list first. Sorry for this, now as a question here. > The company I work for used to only use Oracle databases for their program and now also supports PostgreSQL. > With Postgres, we create a database, a user and a schema, which all have the same name. The name is chosen by the customers. > Sometimes, the customers would like to export the data and definitions and import them under a new name. > This works well by using the -O (no owner) option for pg_dump/pg_restore and only exporting and importing the schema. Afterthe import the schema is renamed. So far so simple and easy! > But there is one problem: we have a database function function1, which calls another function function2. To ensure thatthe function call is safe, we set a search_path for function1. > Both functions are created in the main schema (the one that is named by the customer), the search_path is therefore setto this schema: > ALTER FUNCTION function1 SET SEARCH_PATH TO <the schema name that the customer has chosen> > Since the search_path of the function is not renamed when the schema is renamed, I need to know that there is such a search_path,which I then manually change to the new schema name. > Would it be possible that there is a variable that designates the schema in which a function is located? Like this, forexample: > ALTER FUNCTION function1 SET SEARCH_PATH TO $FUNCTION_SCHEMA; > Since $FUNCTION_SCHEMA would change to denote the new schema when renaming the schema I wouldn't have to change the search_pathmanually. From https://www.postgresql.org/docs/current/sql-alterfunction.html " configuration_parameter value Add or change the assignment to be made to a configuration parameter when the function is called. If value is DEFAULT or, equivalently, RESET is used, the function-local setting is removed, so that the function executes with the value present in its environment. Use RESET ALL to clear all function-local settings. SET FROM CURRENT saves the value of the parameter that is current when ALTER FUNCTION is executed as the value to be applied when the function is entered. See SET and Chapter 20 for more information about allowed parameter names and values. " Not sure if that would meet your requirements. I could see doing in a session: SET search_path = 'main_schema'; ALTER FUNCTION function SET search_path FROM CURRENT; > ________________________________________________________ > Your E-Mail. Your Cloud. Your Office. eclipso Mail & Cloud. https://www.eclipso.de > > > > -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: