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 1rJvRG-00H0Mp-1B for pgsql-general@arkaria.postgresql.org; Sun, 31 Dec 2023 13:01:10 +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 1rJvQE-007sRG-Ea for pgsql-general@arkaria.postgresql.org; Sun, 31 Dec 2023 13:00:06 +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 1rJvQD-007sR7-QB for pgsql-general@lists.postgresql.org; Sun, 31 Dec 2023 13:00:05 +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 1rJvQA-00D3zM-LJ for pgsql-general@lists.postgresql.org; Sun, 31 Dec 2023 13:00:04 +0000 Received: from mail.eclipso.de (www1.eclipso.de [217.69.254.102]) by mail.eclipso.de with ESMTP id 4946D439 for ; Sun, 31 Dec 2023 14:00:00 +0100 (CET) Date: Sun, 31 Dec 2023 14:00:00 +0100 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eclipso.de; s=mail; t=1704027600; bh=GrKOpjoc46Xl1ncDVNcJxmDfS2OkR5vBGiQK+pIWd+E=; h=Date:From:Subject:Reply-To:To:Cc:In-Reply-To:References:From; b=fTGk4Rkw20npvCtHDTPbjbBkc2o8C4K3P49gctO841K85yLyYyT70Ehlgxjwqw5Wb wSZzAEErwCU77hQx2I9cWb1eGgW1aNMhdGCY9aYuSzbKG0PMquBBO5dZtkTCHvzjwI kr9ShEU3tup+S07cIrilZCFbIdfoMI2X+41hECYeapFVl7RlQd/U5fpVsB6IIu76VG f1G9gC9EZkRensMwwXdyFrZ9zOE37NB0ntRGrMr4mQ1ze+uFGorEBIam7Wi540F4QO WG0F/yoktaHxznZhl/LXeP0SoHU0ttzP2g+VC8Di0OfnAp/TemoyebnIGH48JLd2sH gobP8duzq02kw== MIME-Version: 1.0 Message-ID: <4e095fdb84fb5b587e8aa975a2940fc2@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: <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> <9402749f7bc1ec4fb28989f72aa50676@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 That's really nice of you to point me to this mailing list! I'll make my su= ggestion there in the next few days. I don't think it's so bad if the new feature is not available until fall 20= 24 or even later, the time in which it's useful would be very long in any c= ase. Happy New Year to you and thanks again Wilma --- Urspr=C3=BCngliche Nachricht --- Von: Adrian Klaver Datum: 30.12.2023 19:05:28 An: Wilma Wantren Betreff: Re: Changing a schema's name with function1 calling function2 On 12/30/23 08:01, Wilma Wantren wrote: > 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 search_path of a function to select - when executing the func= tion - the schema the function actually is in. ("when executing", and not "when setting the search_path") > This would have been very easy to use and in the implementation of __func= tion_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_path, and to redetermine and cache the variable value only when the function's schema changes. This is still not out of the realm of possibility, it would require=20 getting a developer or developers interested in it. The place to make=20 that argument is the hackers list: https://www.postgresql.org/list/pgsql-hackers/ Though the earliest that could be incorporated into Postgres would be=20 the next major release Fall of 2024. This is dependent on getting the=20 code in before the feature freeze Spring(?) of 2024. > Instead, I should now call the - actually diagnostic - function PG_ROUTIN= E_OID from the body of my function, with which I get the OID of my function in order to then determine the schema of my function and set it as search_path= . I don't think that suits my requirements. >=20 > I will therefore consider using a database change management system instead (e.g. sqitch, suggested by Adrian) and defining there what should happen when the schema name is changed, including the names of all function= s whose search_path is to be changed. >=20 > Many thanks again > Wilma >=20 --=20 Adrian Klaver adrian.klaver@aklaver.com ________________________________________________________ Your E-Mail. Your Cloud. Your Office. eclipso Mail Europe. https://www.ecli= pso.de