Changing a schema's name with function1 calling function2 - Mailing list pgsql-general

From Wilma Wantren
Subject Changing a schema's name with function1 calling function2
Date
Msg-id 07aac10034aaf87697ae401475f48909@mail.eclipso.de
Whole thread Raw
Responses Re: Changing a schema's name with function1 calling function2
List pgsql-general
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 that
thefunction 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 set
tothis 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, for
example:
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. 
________________________________________________________
Your E-Mail. Your Cloud. Your Office. eclipso Mail & Cloud. https://www.eclipso.de





pgsql-general by date:

Previous
From: veem v
Date:
Subject: Re: Read write performance check
Next
From: Tom Lane
Date:
Subject: Re: PostgreSQL 15.5 stops processing user queries