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 1rJbmt-00EQBR-1w for pgsql-general@arkaria.postgresql.org; Sat, 30 Dec 2023 16:02:11 +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 1rJbmq-000xDI-UQ for pgsql-general@arkaria.postgresql.org; Sat, 30 Dec 2023 16:02:08 +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 1rJbmq-000xD9-Hi for pgsql-general@lists.postgresql.org; Sat, 30 Dec 2023 16:02:08 +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 1rJbmi-00Cvho-Sr for pgsql-general@lists.postgresql.org; Sat, 30 Dec 2023 16:02:07 +0000 Received: from mail.eclipso.de (www1.eclipso.de [217.69.254.102]) by mail.eclipso.de with ESMTP id 78D232D1 for ; Sat, 30 Dec 2023 17:01:57 +0100 (CET) Date: Sat, 30 Dec 2023 17:01:57 +0100 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eclipso.de; s=mail; t=1703952117; bh=MI1YyAExtzknJWp3g5jI2ov1w/2OUQPZrc3j134twHk=; h=Date:From:Subject:Reply-To:To:Cc:In-Reply-To:References:From; b=ZMLCp8UjKF66sxIYZXAIPFCA7e0H8c7T9I+JotV444C8v2GAE1S2w0/sKCCxYvTBK EwQ+ZFSslFytjnMTiS6T9mW3bzNyLfr0H63GYFIbo1DwbBp6dqkmI3D1U074t7AU6Q Nv+XIeB9lAyxHkE90Mz26aGTrPJryuaoHym42frowT6J7zY30ObV7eNDhNAS/ChWRp B5t2sNd0gS+LXqW+znXnrFiT12QUbFHk8ETfg4VNDDB5+LIeppzO3YvcJ42JCCzbS8 OWdPUIW+yIVYvNKR2UUZwR3PEyWHlCR8i4VeLr5fYmzMswL6jebs7a/QLAXK8ylD2p vupsWy6zmlOng== MIME-Version: 1.0 Message-ID: <9402749f7bc1ec4fb28989f72aa50676@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: <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> <924F91AE-0597-45EE-B8ED-D857EFDCFB09@thebuild.com> 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 all, and especially you, Adrian, for your answers. However, I find the last suggestion too complicated. In Peter's words I had= suggested a "magic variable" __function_schema__ which can be set as the s= earch_path of a function to select - when executing the function - the sche= ma the function actually is in. ("when executing", and not "when setting th= e search_path") =20 This would have been very easy to use and in the implementation of __functi= on_schema__ it would have been possible to determine and cache the variable= value (i.e. the schema of the function) directly when setting the search_p= ath, and to redetermine and cache the variable value only when the function= 's schema changes. Instead, I should now call the - actually diagnostic - function PG_ROUTINE_= OID from the body of my function, with which I get the OID of my function i= n order to then determine the schema of my function and set it as search_pa= th. I don't think that suits my requirements. I will therefore consider using a database change management system instead= (e.g. sqitch, suggested by Adrian) and defining there what should happen w= hen the schema name is changed, including the names of all functions whose = search_path is to be changed. Many thanks again Wilma --- Urspr=C3=BCngliche Nachricht --- Von: Adrian Klaver Datum: 27.12.2023 16:40:55 An: Kirk Wolak , Christophe Pettus Betreff: Re: Changing a schema's name with function1 calling function2 On 12/26/23 22:41, Kirk Wolak wrote: > On Mon, Dec 25, 2023 at 1:47=E2=80=AFPM Christophe Pettus > wrote: >=20 >=20 >=20 > > On Dec 25, 2023, at 10:44, Adrian Klaver > > wrote: > > Functions with same name in different schemas would need to be > dealt with. >=20 > I think that's the primary use-case (at least, it would be for me), > and I don't see a convenient way of doing that.=C2=A0 Even a "get OID of > current function" function would be useful here. >=20 > And Pavel Already Created it: > GET DIAGNOSTIC PID =3D PG_ROUTINE_OID=C2=A0; To be clear the above is for Postgres 16+ and for the plpgsql language only= . >=20 > If I understand the issue correctly. >=20 > Kirk Out! --=20 Adrian Klaver adrian.klaver@aklaver.com ________________________________________________________ Your E-Mail. Your Cloud. Your Office. eclipso Mail Europe. https://www.ecli= pso.de