If I want to change the name of my database schema, I call
alter schema my_schema rename to other_schema
However, there is a problem with functions that call other functions in the
same schema. These functions have a search_path
alter function my_schema.function1 set search_path to my_schema
If the name of the schema is changed with "alter schema...", the search path of
the functions is not changed, so I still have to call the following after
renaming the schema:
alter function other_schema.function1 set search_path to other_schema
This is worse than it seems at first glance, because I need to know which
functions have a search_path. If my list of these functions is incomplete and I
therefore do not change the search_path for all functions, there will be an
error in the schema after renaming the schema.
I am sure that in the vast majority of cases where a function has a
search_path, this search_path specifies the schema in which the function is
located, i.e. the function
my_schema.function1
has search_path
my_schema
It would therefore be great if you could implement a "magic variable" called
__function_schema__, which can be set as the search_path of a function and
which is not evaluated from the outset, but is transferred unchanged to the
metadata of the function:
Metadata of function1:
...
search_path: __function_schema__
...
Each time the function is executed, the variable value is determined.
Therefore, the search_path is always correct: as long as the function is in the
schema my_schema, the search_path __function_schema__ is evaluated to my_schema
when the function is executed, and as soon as the function is in the schema
other_schema after the schema has been renamed, the search_path
__function_schema__ is evaluated to other_schema when the function is executed.
Of course, the implementation could cache the value of __function_schema__ for
each function and only change it when the schema of the function changes.
Wilma
PS Even though I wrote that I would like to have a "magic variable" called
__function_schema__, I would of course also be very happy with a name other
than __function_schema__.
________________________________________________________
Your E-Mail. Your Cloud. Your Office. eclipso Mail Europe.
https://www.eclipso.de
