From: Wilma Wantren <wilma.wantren@eclipso.de>
To: pgsql-general@lists.postgresql.org
Subject: Changing a schema's name with function1 calling function2
Date: Thu, 21 Dec 2023 14:39:56 +0100
Message-ID: <9ed1070f8561f048e72c3a3683565d97@mail.eclipso.de> (raw)
<html><span style="font-family:arial,helvetica,sans-serif; font-size:12px"></span>I accidentally posted the following to the bugs mailing list first. Sorry for this, now as a question here.<br />The company I work for used to only use Oracle databases for their program and now also supports PostgreSQL.<br />With Postgres, we create a database, a user and a schema, which all have the same name. The name is chosen by the customers.<br />Sometimes, the customers would like to export the data and definitions and import them under a new name.<br />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!<br />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.<br />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:<br />ALTER FUNCTION function1 SET SEARCH_PATH TO <the schema name that the customer has chosen><br />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.<br />Would it be possible that there is a variable that designates the schema in which a function is located? Like this, for example:<br />ALTER FUNCTION function1 SET SEARCH_PATH TO $FUNCTION_SCHEMA;<br />Since $FUNCTION_SCHEMA would change to denote the new schema when renaming the schema I wouldn't have to change the search_path manually.<br /><br/><br/><hr style='height:1px;background:#DDDDDD;border:none;width:100%;'><br /><span style="font-size:12px;">Your E-Mail. Your Cloud. Your Office. <b><a href="https://www.eclipso.de"; style="text-decoration:none; color:#153270;">eclipso Mail & Cloud</a></b>.</span><br /></html>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: wilma.wantren@eclipso.de
Subject: Re: Changing a schema's name with function1 calling function2
In-Reply-To: <9ed1070f8561f048e72c3a3683565d97@mail.eclipso.de>
References: <9ed1070f8561f048e72c3a3683565d97@mail.eclipso.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox