On 2023-12-23 08:31:39 -0800, Adrian Klaver wrote:
> On 12/23/23 08:12, Wilma Wantren wrote:
> > I had already feared that such a variable does not exist (because I
> > had not found it). I think that's a pity, because I suspect that in
> > at least 90% of the cases where a function needs a search_path, this
> > variable would be the value of the search_path, so that in 90% of
> > the cases no change to the search_path would be necessary after
> > renaming the schema.
>
> I would say the issue is with this from your previous post:
>
>
> "Therefore it would be great if there was a variable which I could set in a
> search_path (like the variable "$user") which denotes the function's schema
> and which is only evaluated when the function is executed, i.e. the variable
> would be the value of the function's search_path in the function's meta
> data."
>
> search_path can be set(just off the top of head):
>
> postgresql.conf and it's include files
> ALTER DATABASE
> ALTER FUNCTION
> ALTER ROLE
> ALTER SYSTEM
> At any point by a user/script/function/etc.
>
> Even if such a variable existed you be chasing down the 'correct' version of
> search_path that had it. Basically where you are now.
I think you misunderstood Wilma. What she is asking for is a "keyword"
or "magic variable" (or whatever you want to call it) which you can
specify in CREATE|ALTER FUNCTION ... SET SEARCH_PATH = ..., which refers
to the schema the function is (being) created in.
So if you create the function with
create function foo (...)
set search_path to __function_schema__
$$ ... $$
(using the "dunder" convention (from some other programming languages)
to denote the magic variable/keyword)
the search path would be set to whatever schema was first in the
search_path when the function was created.
If you create it with
create function my_schema.foo (...)
set search_path to __function_schema__
$$ ... $$
it would be set to "my_schema".
And if you changed the schema with
alter function foo set schema my_new_schema
it would be changed to "my_new_schema".
Personally I think that effect should be easy enough to create in your
deployment or migration scripts but of course that assumes that you have
such scripts. If you are doing your deployments manually (especially by
cloning a template as described by Wilma) I can see how that feature
would make things easier and/or reduce the risk of errors.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
