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
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 PM Christophe Pettus > wrote:
>
>
>
> > On Dec 25, 2023, at 10:44, Adrian Klaver
> mailto:adrian.kla...@aklaver.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.kla...@aklaver.com
Your E-Mail. Your Cloud. Your Office. eclipso Mail Europe.
https://www.eclipso.de
Re: Need help
On 12/29/23 21:56, ankit.si...@nest-is2.com wrote: It's giving one more error please have a look. All that is telling you is that the underlying error you first reported "pg restore unsupported 1.15" caused one of the Zulip commands to fail. The issue still remains that you have a mismatch between the file format of the dump file and the version of pg_restore you are using to attempt to restore that file. Until that gets sorted out you will not get any further. -- Adrian Klaver adrian.kla...@aklaver.com
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 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.kla...@aklaver.com
