Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rGoee-00HHJs-4G for pgsql-general@arkaria.postgresql.org; Fri, 22 Dec 2023 23:10:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rGoea-00COT3-2l for pgsql-general@arkaria.postgresql.org; Fri, 22 Dec 2023 23:10:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rGoeZ-00COP7-D2 for pgsql-general@lists.postgresql.org; Fri, 22 Dec 2023 23:10:03 +0000 Received: from mail.eclipso.de ([217.69.254.104]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rGoeV-00BjZd-JH for pgsql-general@lists.postgresql.org; Fri, 22 Dec 2023 23:10:01 +0000 Received: from mail.eclipso.de (www1.eclipso.de [217.69.254.102]) by mail.eclipso.de with ESMTP id 456031EF for ; Sat, 23 Dec 2023 00:09:56 +0100 (CET) Date: Sat, 23 Dec 2023 00:09:56 +0100 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eclipso.de; s=mail; t=1703286596; bh=IHe++iyOvD64k9of0nwCfDb9t2C28g3O4M9T7MX1ldI=; h=Date:From:Subject:Reply-To:To:Cc:In-Reply-To:References:From; b=aDJYzdXk9VE5q7aqfoBNlSJJeOH+tN24VOnX4uVmvKcdRpOiix2avfpiO9+8qCkcR y7DN3uYBF8lt7xFvkiTy38VfNV85APRMuIu/qFe0GzWCHG7cIoqoptjNhXHpm7Vw/z pDwPvNPrLuab5GRYBWRr3Re3QchXQPpSizAFMpQuhwpm/RhbnYiLMkuLV7wcxj8PHc Wqj40GLRsP1vMfO9vVDZpx1vIs+fBPgtnYkgORwMbpYSlzGxZhGTGyLdfLCSP+lNX8 LvFC0h9CfoqEINMnfuK2XOB+e0nemtZJGr73iTVZBll+xmiLvidXt93C2poSMtsxJA P6cchAFYirvCw== MIME-Version: 1.0 Message-ID: <4cb2e7242b54e84f9d0c161cc56f38e4@mail.eclipso.de> X-Mailer: eclipso / 7.4.0 From: "Wilma Wantren" Subject: Re: Re: Changing a schema's name with function1 calling function2 Reply-To: "Wilma Wantren" To: "Adrian Klaver" Cc: In-Reply-To: References: <9ed1070f8561f048e72c3a3683565d97@mail.eclipso.de> <07aac10034aaf87697ae401475f48909@mail.eclipso.de> Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Thank you for your response!=20 No, this does not help me. I will try to explain better what I mean. Our cu= stomers use our program with a Postgres database, user and schema, which ha= ve the same name. The customer chooses the name. Let's say one customer cal= ls the database, user and schema 'my_things'.=20 I want to create scripts for the customers with which they can export the s= chema and import it into another database with a new schema name, let's say= one customer wants to import it as 'my_things1' (this schema belongs to da= tabase 'my_things1'). The export script calls pg_dump to export schema 'my_things'.=20 The import script calls pg_restore to restore schema 'my_things' in databas= e 'my_things1' and then calls psql to change the schema name to 'my_things1= '. Now there is function1 which its search_path set to 'my_things'. Because th= e search_path is still set to 'my_things' after renaming the schema the scr= ipt must now call psql to change the function's search_path to 'my_things1'= .=20 This is not just one line more in the import script. It is a fact that I must know - if I did not know about function1's search_= path then there would be an error in the schema after renaming the schema.= =20 And imagine if one day a colleague of mine implements a new function which = needs a search_path but the colleague forgets to adjust the import script t= hen again there is an error in the schema after renaming the schema.=20 Therefore it would be great if there was a variable which I could set in a = search_path (like the variable "$user") which denotes the function's schema= and which is only evaluated when the function is executed, i.e. the variab= le would be the value of the function's search_path in the function's meta = data. This variable, e.g. "$function_schema" would still denote the correct= schema after renaming the schema. --- Urspr=C3=BCngliche Nachricht --- Von: Adrian Klaver Datum: 21.12.2023 17:30:02 An: Wilma Wantren , pgsql-general@lists.postgres= ql.org Betreff: Re: Changing a schema's name with function1 calling function2 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 progra= m 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 > 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 renamin= g the schema I wouldn't have to change the search_path manually. From https://www.postgresql.org/docs/current/sql-alterfunction.html " configuration_parameter value Add or change the assignment to be made to a configuration=20 parameter when the function is called. If value is DEFAULT or,=20 equivalently, RESET is used, the function-local setting is removed, so=20 that the function executes with the value present in its environment.=20 Use RESET ALL to clear all function-local settings. SET FROM CURRENT=20 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 =3D 'main_schema'; ALTER FUNCTION function SET search_path FROM CURRENT; > ________________________________________________________ > Your E-Mail. Your Cloud. Your Office. eclipso Mail & Cloud. https://www.e= clipso.de >=20 >=20 >=20 >=20 --=20 Adrian Klaver adrian.klaver@aklaver.com ________________________________________________________ Your E-Mail. Your Cloud. Your Office. eclipso Mail & Cloud. https://www.ecl= ipso.de