From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Wilma Wantren <wilma.wantren@eclipso.de>
Cc: wolakk@gmail.com
Cc: xof@thebuild.com
Cc: hjp-pgsql@hjp.at
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Changing a schema's name with function1 calling function2
Date: Sat, 30 Dec 2023 10:05:28 -0800
Message-ID: <bfebadee-671a-41d4-9082-2d5c147618b9@aklaver.com> (raw)
In-Reply-To: <9402749f7bc1ec4fb28989f72aa50676@mail.eclipso.de>
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>
<b89c4a7f-2663-44d7-8a4e-07fa657b5b29@aklaver.com>
<924F91AE-0597-45EE-B8ED-D857EFDCFB09@thebuild.com>
<CACLU5mRBrLfrBg8RcfqefXutus7Vzfg+DG10Ci64hXNN7eqRuw@mail.gmail.com>
<c2954d6e-a739-431e-829a-02919d58b48b@aklaver.com>
<9402749f7bc1ec4fb28989f72aa50676@mail.eclipso.de>
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 function - 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 __function_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
getting a developer or developers interested in it. The place to make
that argument is the hackers list:
https://www.postgresql.org/list/pgsql-hackers/
Though the earliest that could be incorporated into Postgres would be
the next major release Fall of 2024. This is dependent on getting the
code in before the feature freeze Spring(?) of 2024.
> 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 in order to then determine the schema of my function and set it as search_path. 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 when the schema name is changed, including the names of all functions whose search_path is to be changed.
>
> Many thanks again
> Wilma
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, wilma.wantren@eclipso.de, wolakk@gmail.com, xof@thebuild.com, hjp-pgsql@hjp.at
Subject: Re: Changing a schema's name with function1 calling function2
In-Reply-To: <bfebadee-671a-41d4-9082-2d5c147618b9@aklaver.com>
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> <b89c4a7f-2663-44d7-8a4e-07fa657b5b29@aklaver.com> <924F91AE-0597-45EE-B8ED-D857EFDCFB09@thebuild.com> <CACLU5mRBrLfrBg8RcfqefXutus7Vzfg+DG10Ci64hXNN7eqRuw@mail.gmail.com> <c2954d6e-a739-431e-829a-02919d58b48b@aklaver.com> <9402749f7bc1ec4fb28989f72aa50676@mail.eclipso.de> <bfebadee-671a-41d4-9082-2d5c147618b9@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox