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
stillhave 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
thesefunctions is incomplete and I therefore do not change the search_path for all functions, there will be an error in
theschema 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
inwhich 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
thesearch_path of a function and which is not evaluated from the outset, but is transferred unchanged to the metadata
ofthe 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
longas the function is in the schema my_schema, the search_path __function_schema__ is evaluated to my_schema when the
functionis 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
theschema 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
bevery happy with a name other than __function_schema__.
________________________________________________________
Your E-Mail. Your Cloud. Your Office. eclipso Mail Europe. https://www.eclipso.de
