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. After the 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 the 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 set to 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, 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_path manually.
________________________________________________________
Your E-Mail. Your Cloud. Your Office. eclipso Mail & Cloud.
https://www.eclipso.de
