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 1rHpwO-004VUx-8f for pgsql-general@arkaria.postgresql.org; Mon, 25 Dec 2023 18:44: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 1rHpw2-004Yaj-5g for pgsql-general@arkaria.postgresql.org; Mon, 25 Dec 2023 18:44:18 +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 1rHpw0-004Yab-GU for pgsql-general@lists.postgresql.org; Mon, 25 Dec 2023 18:44:17 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rHpvt-00CADR-6Y for pgsql-general@lists.postgresql.org; Mon, 25 Dec 2023 18:44:11 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.nyi.internal (Postfix) with ESMTP id 28FE05C008C; Mon, 25 Dec 2023 13:44:07 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute3.internal (MEProxy); Mon, 25 Dec 2023 13:44:07 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc: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=1703529847; x=1703616247; bh=UMTtrz7Pp2czNW/A0oNGzo4S7AZCeC9c72JJTHssc7I=; b= WLQ/laG+XlZrGvhrGL48yGnWXeIXu34o6rpxVPI+CW1ybVAY7cQvpK+i56l1E1Ft kq26Vh+U6zoU9qcX6VK4Ldey+QnNEiqh9r387E69ApDTsx4UJthZlsYrKf9JY5gf QQbKhxCEfcz0s7/UeeVNBg8q9yaDIDiT2PHHEsvB4g57Q5KRYocViQZN37SnX+4V DTXXkfRNzzqiGKM/tJEyi3UbpwMW65mBblMnNvEfuFYIUDiq7LSYc77N06pO4gFl DTrYmCLgMu6FodjMTCiOc6suK17j8Md5nW+2asVyfzLEjF1BkeL76t9EDWK7p5xs edhrjS5+b6hS5ICwbyS+qQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc: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=1703529847; x= 1703616247; bh=UMTtrz7Pp2czNW/A0oNGzo4S7AZCeC9c72JJTHssc7I=; b=P c7vY272YOaOJzZcQVMBj/WSEiWCMx7PW4/Kzcymi7bjWyQiGig8hbw7dW3qM4Nnh VQkmwvktdYHoXLlsQdesRHcIM0AgWOeKn65fGNwIFbjGaH4CDuJMsDzrqdbmcw90 WBjBMD2DYY1xXJeJNEuwRAM11zhuf8a5oKWlCGMWTSIOXRC2IoX++QAyH2AlOAz1 EkKPgALHKNOm21xdS/4YnFtIVfArAkzeZJV1PG5A2ijsBQJhIw2U6+ojQem9mdcr xzftXUhwfye+W14LcBLDVIpovGvmHDagP+i62ab6hxcx4RoPheHf86wiqkqsS79B NK1YglVrYPNxdKOUQMotA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrvddvfedguddukecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmd enucfjughrpefkffggfgfuvfevfhfhjggtgfesthejredttddvjeenucfhrhhomheptegu rhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrd gtohhmqeenucggtffrrghtthgvrhhnpefhveeihfevlefffedthedukeevfefhfedtgeei feevteffgfefhedtffetudettdenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmh epmhgrihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 25 Dec 2023 13:44:05 -0500 (EST) Message-ID: Date: Mon, 25 Dec 2023 10:44:04 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Changing a schema's name with function1 calling function2 To: Wilma Wantren , "Peter J. Holzer" Cc: 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> <9ed20788-82cd-4eba-a590-7a474ca570ef@aklaver.com> <20231225143726.gfynym24xqcc27tu@hjp.at> <9bb4fd9a3e59adce379c3828e2a73646@mail.eclipso.de> Content-Language: en-US From: Adrian Klaver In-Reply-To: <9bb4fd9a3e59adce379c3828e2a73646@mail.eclipso.de> 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/25/23 09:29, Wilma Wantren wrote: > Great that you have discussed this further! And sorry that what I wrote is apparently still misleading. > Peter understood me correctly, "__function_schema__ always refers to the schema the function actually is in". > I define: > 1. create function my_schema.function1... > 2. alter function function1 set search_path to __function_schema__ > The variable __function_schema__ is not evaluated, but is set "as it is" in the metadata of the function, which therefore reads: > ... > search_path: __function_schema__ > ... > Only when function1 is executed the variable is evaluated, namely "to the schema the function actually is in", in this case to 'my_schema'. If the schema is renamed, the search_path of function1 does not have to be changed, and is nevertheless evaluated correctly again when the function is executed, now to the new schema name. Alright I see now. A simple example of a partial solution: CREATE OR REPLACE FUNCTION public.schema_test() RETURNS void LANGUAGE plpgsql AS $function$ DECLARE fnc_name varchar := 'schema_test'; sch_name varchar; cur_sch name; BEGIN SELECT INTO sch_name pronamespace::regnamespace from pg_proc where proname = fnc_name; RAISE NOTICE 'Schema is %', sch_name; PERFORM set_config('search_path', sch_name, true); select into cur_sch current_schema; RAISE NOTICE 'search_path = %', cur_sch; END; Issues: fnc_name would need to kept updated. Overloaded function names would need to be accounted for. Functions with same name in different schemas would need to be dealt with. At this point I still the solution as being external to the database. In other words making the change before the objects are loaded into a database. -- Adrian Klaver adrian.klaver@aklaver.com