From: | "Wilma Wantren" <wilma(dot)wantren(at)eclipso(dot)de> |
---|---|
To: | <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Changing a schema's name with function1 calling function2 |
Date: | 2024-01-08 08:05:47 |
Message-ID: | e54ab1d609355d32e551feebb5ac84f6@mail.eclipso.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Guo | 2024-01-08 08:32:37 | Re: Oversight in reparameterize_path_by_child leading to executor crash |
Previous Message | Bertrand Drouvot | 2024-01-08 07:59:10 | Re: verify predefined LWLocks have entries in wait_event_names.txt |