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 1rHWwe-002rpL-3P for pgsql-general@arkaria.postgresql.org; Sun, 24 Dec 2023 22:27:40 +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 1rHWwb-00EXq4-Jj for pgsql-general@arkaria.postgresql.org; Sun, 24 Dec 2023 22:27:37 +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 1rHWwZ-00EXpv-Sg for pgsql-general@lists.postgresql.org; Sun, 24 Dec 2023 22:27:37 +0000 Received: from wout5-smtp.messagingengine.com ([64.147.123.21]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rHWwQ-00Dixo-26 for pgsql-general@lists.postgresql.org; Sun, 24 Dec 2023 22:27:34 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.west.internal (Postfix) with ESMTP id 650A23200B0D for ; Sun, 24 Dec 2023 17:27:21 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute1.internal (MEProxy); Sun, 24 Dec 2023 17:27:21 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm1; t=1703456840; x=1703543240; bh=Y2qEQQIA+ixB1z747WTUegKczjiV0vHbV0nyaFs2vIM=; b= GXtvQqXCHfgwuQ4cPd3C4ecaI5PCq7y7yyyRieppRBoK0L8pVwPdwqGeMbEh319B prS6e1aVBwSn40ehyoqs21HwqYs2pdJF2RvXWAfTQJPB30AasSWbUV0CSHEJ4TT1 pQPKsLECzyxR9JYXYt5S0lSnJpSuDSur+9G0ZE4il2wny3k3tNC8Ury7AR11BG7z kiNK6Mip7f4maoIcTKzE4VoWj+cybjQ3dCFr1b1pm8leaKkSnlztI/UKe0D0wzYR nyMhZBso4THrT7qtaS+Pk+doxAi1XIUuscYxjqe7WFrpDYDSYIEUw8Nfj33mPxvA vT2nM1OQZyQ5FfB9xqDd4A== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t=1703456840; x= 1703543240; bh=Y2qEQQIA+ixB1z747WTUegKczjiV0vHbV0nyaFs2vIM=; b=s mZimURbFfWG345jlV5uUv9onhOt8OHNs4oUxCis1q+PjzY5nx5IeIHU+jL9fmyTI oz52TbRegzbYk6fStISq24QK6u8Z2N7b0ASq9f+AKew8mra1snmznbJv2zopsBh0 ySvbAppmxy3DeG6v+KcyAi27JycdxQZKfa9h+PJ9e4xkLm+VSkl1yQNobEKNPcCS wYZILzwF477hY+25cPhTTe1scplhl6Z5cRSRjX0f5K9SYFbGw7nemPas76uuFmM9 26IkUrre1JcUm0lkZaTiawANXv2aq5Maar+MJiOhVgX5vX8EcU01w7e4pK+Q8z6t DLbPNyK7RXDq4FGIH/yLQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrvddvuddgudeitdcutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecunecujfgurhepkfffgggfuffvfhfhjggtgfesth ejredttddvjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdr khhlrghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpeekfeehud dvjeeigfeifeejtdduudffledvfeelheeftdeiffeugfdvkeelgedtvdenucffohhmrghi nhepphhoshhtghhrvghsqhhlrdhorhhgnecuvehluhhsthgvrhfuihiivgeptdenucfrrg hrrghmpehmrghilhhfrhhomheprggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdr tghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA for ; Sun, 24 Dec 2023 17:27:20 -0500 (EST) Message-ID: <9ed20788-82cd-4eba-a590-7a474ca570ef@aklaver.com> Date: Sun, 24 Dec 2023 14:27:19 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Changing a schema's name with function1 calling function2 To: pgsql-general@lists.postgresql.org References: <9ed1070f8561f048e72c3a3683565d97@mail.eclipso.de> <07aac10034aaf87697ae401475f48909@mail.eclipso.de> <4cb2e7242b54e84f9d0c161cc56f38e4@mail.eclipso.de> <09a7102b697d2b2acd81cb12344b0170@mail.eclipso.de> <90ca8707-6f44-47c2-b781-0138947d7483@aklaver.com> <20231224214332.rokhqwkq2xewzobk@hjp.at> Content-Language: en-US From: Adrian Klaver In-Reply-To: <20231224214332.rokhqwkq2xewzobk@hjp.at> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 12/24/23 13:43, Peter J. Holzer wrote: > On 2023-12-23 08:31:39 -0800, Adrian Klaver wrote: > > I think you misunderstood Wilma. What she is asking for is a "keyword" > or "magic variable" (or whatever you want to call it) which you can > specify in CREATE|ALTER FUNCTION ... SET SEARCH_PATH = ..., which refers > to the schema the function is (being) created in. Maybe, but I don't think so. From one of Wilma's previous post: "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." It seems to be pretty tied to search_path. Now there is, from: https://www.postgresql.org/docs/current/catalog-pg-proc.html proconfig text[] Function's local settings for run-time configuration variables Though that is part of the function definition not the code inside the function as you show below. Also you would still need to determine what was the appropriate schema before creating/altering the setting for the function. There is also: pronamespace oid (references pg_namespace.oid) The OID of the namespace that contains this function which could be construed as '... the function meta data.', though its not strictly search_path. I could see that being part of some dynamic SQL to set the search_path. > > So if you create the function with > > create function foo (...) > set search_path to __function_schema__ > $$ ... $$ > > (using the "dunder" convention (from some other programming languages) > to denote the magic variable/keyword) > > the search path would be set to whatever schema was first in the > search_path when the function was created. That assumes the first schema is the correct one. Not something that could be counted on given search_path can be set any number of ways at any time. > > If you create it with > > create function my_schema.foo (...) > set search_path to __function_schema__ > $$ ... $$ > > it would be set to "my_schema". > > And if you changed the schema with > > alter function foo set schema my_new_schema > > it would be changed to "my_new_schema". > > Personally I think that effect should be easy enough to create in your > deployment or migration scripts but of course that assumes that you have > such scripts. If you are doing your deployments manually (especially by > cloning a template as described by Wilma) I can see how that feature > would make things easier and/or reduce the risk of errors. > > hp > -- Adrian Klaver adrian.klaver@aklaver.com