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 1rH4cI-000v7N-Os for pgsql-general@arkaria.postgresql.org; Sat, 23 Dec 2023 16:12:47 +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 1rH4cG-002cii-RP for pgsql-general@arkaria.postgresql.org; Sat, 23 Dec 2023 16:12:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rH4cG-002cia-ER for pgsql-general@lists.postgresql.org; Sat, 23 Dec 2023 16:12:44 +0000 Received: from mail.eclipso.de ([217.69.254.104]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rH4cB-00DX3k-RM for pgsql-general@lists.postgresql.org; Sat, 23 Dec 2023 16:12:43 +0000 Received: from mail.eclipso.de (www1.eclipso.de [217.69.254.102]) by mail.eclipso.de with ESMTP id 07CD6F39 for ; Sat, 23 Dec 2023 17:12:38 +0100 (CET) Date: Sat, 23 Dec 2023 17:12:38 +0100 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eclipso.de; s=mail; t=1703347958; bh=NqaqPZsq68S5866wqXF1fi4XXeCGLzJ4UECOibFyMNE=; h=Date:From:Subject:Reply-To:To:Cc:In-Reply-To:References:From; b=snUawhMLCR0TmiMX5TWuV06l/GnLwj/u4vlAqjblEUQfQ89hSLQrzSLyp4jZzohSK UxATOfjOO8Kd17Uq9uvRTnT6oprgoumgs/SMeIIC8s0nRGvo3ubUFk2rkK47SdUd8B zqrjb97saXlVJuJvseQZgcvKjmy4N3CX398Gt6xmccI48Xj4o+LNBsJgJRNcpHEiOO a5awMNXmBWzuQ+ewqvcAVMhWQtfAHeQSREc/YGgvtPdbA2OgrdVN6rUxtDJwhnRg/A U7L2h/HnXn4BfjSwIoKx+3DBI9O9PSGE/XXu2VhTF8kSZKUfqvjmd6mld5zI3jtExr vkhDIpRQ50Saw== MIME-Version: 1.0 Message-ID: <09a7102b697d2b2acd81cb12344b0170@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> <4cb2e7242b54e84f9d0c161cc56f38e4@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! I had already feared that such a variable does not exist (because I had not= found it). I think that's a pity, because I suspect that in at least 90% o= f the cases where a function needs a search_path, this variable would be th= e value of the search_path, so that in 90% of the cases no change to the se= arch_path would be necessary after renaming the schema.=20 Many thanks for the reference to sqitch, I'll have a look at that now. =20 Translated with DeepL.com (free version) --- Urspr=C3=BCngliche Nachricht --- Von: Adrian Klaver Datum: 23.12.2023 01:09:12 An: Wilma Wantren Betreff: Re: Changing a schema's name with function1 calling function2 On 12/22/23 15:09, Wilma Wantren wrote: > Thank you for your response! > No, this does not help me. I will try to explain better what I mean. Our customers use our program with a Postgres database, user and schema, which have the same name. The customer chooses the name. Let's say one cust= omer calls the database, user and schema 'my_things'. > I want to create scripts for the customers with which they can export the schema 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 database 'my_things1'). > The export script calls pg_dump to export schema 'my_things'. > The import script calls pg_restore to restore schema 'my_things' in database '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 the search_path is still set to 'my_things' after renaming the schema the script must now call psql to change the function's search_path to 'my_thing= s1'. > 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. > 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 then again there is an error in the schema after renaming the schema= . > 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 variable 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. >=20 There is no such variable. What you have available is what I showed before: https://www.postgresql.org/docs/current/sql-alterfunction.html " configuration_parameter value Add or change the assignment to be made to a configuration parameter when the function is called. If value is DEFAULT or, equivalently, RESET is used, the function-local setting is removed, so that the function executes with the value present in its environment. Use RESET ALL to clear all function-local settings. SET FROM CURRENT 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. " The alternative is to create a migration process using scripts with=20 variable substitution outside the database. I do something similar using Sqitch(https://sqitch.org/) and its template system: https://sqitch.org/docs/manual/sqitch-add/ --=20 Adrian Klaver adrian.klaver@aklaver.com ________________________________________________________ Your E-Mail. Your Cloud. Your Office. eclipso Mail & Cloud. https://www.ecl= ipso.de