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
schemathe 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
determineand cache the variable value (i.e. the schema of the function) directly when setting the search_path, and to
redetermineand 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
whichI get the OID of my function in order to then determine the schema of my function and set it as search_path. I
don'tthink that suits my requirements.
>
> I will therefore consider using a database change management system instead (e.g. sqitch, suggested by Adrian) and
definingthere what should happen when the schema name is changed, including the names of all functions whose
search_pathis to be changed.
>
> Many thanks again
> Wilma
>
--
Adrian Klaver
adrian.klaver@aklaver.com