Re: Re: Changing a schema's name with function1 calling function2

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.
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

--- Ursprüngliche Nachricht ---
Von: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Datum: 27.12.2023 16:40:55
An: Kirk Wolak <wolakk(at)gmail(dot)com>, Christophe Pettus <xof(at)thebuild(dot)com>
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 PM Christophe Pettus <xof(at)thebuild(dot)com

> <mailto:xof(at)thebuild(dot)com>> wrote:
>
>
>
> > On Dec 25, 2023, at 10:44, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
wrote:
> > Functions with same name in different schemas would need to
be
> dealt with.
>
> 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.  Even a "get
OID of
> current function" function would be useful here.
>
> And Pavel Already Created it:
> GET DIAGNOSTIC PID = PG_ROUTINE_OID ;

To be clear the above is for Postgres 16+ and for the plpgsql language only.

>
> If I understand the issue correctly.
>
> Kirk Out!

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

________________________________________________________
Your E-Mail. Your Cloud. Your Office. eclipso Mail Europe. https://www.eclipso.de