pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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:47:55 +0100
Message-ID: <07aac10034aaf87697ae401475f48909@mail.eclipso.de> (raw)
References: <9ed1070f8561f048e72c3a3683565d97@mail.eclipso.de>

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







view thread (18+ messages)  latest in thread

Message-ID: <07aac10034aaf87697ae401475f48909@mail.eclipso.de>
Permalink:  ../07aac10034aaf87697ae401475f48909@mail.eclipso.de/
Also on:    postgresql.org/message-id/07aac10034aaf87697ae401475f48909@mail.eclipso.de

 · 

reply

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: <07aac10034aaf87697ae401475f48909@mail.eclipso.de>
  References: <9ed1070f8561f048e72c3a3683565d97@mail.eclipso.de> <07aac10034aaf87697ae401475f48909@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