Thread: Changing a schema's name with function1 calling function2

Changing a schema's name with function1 calling function2

From
"Wilma Wantren"
Date:
Now as text mail, sorry.
I accidentally posted the following to the bugs mailing list first. Sorry for this, now as a question here.
The company I work for used to only use Oracle databases for their program and now also supports PostgreSQL.
With Postgres, we create a database, a user and a schema, which all have the same name. The name is chosen by the
customers.
Sometimes, the customers would like to export the data and definitions and import them under a new name.
This works well by using the -O (no owner) option for pg_dump/pg_restore and only exporting and importing the schema.
Afterthe import the schema is renamed. So far so simple and easy! 
But there is one problem: we have a database function function1, which calls another function function2. To ensure that
thefunction call is safe, we set a search_path for function1. 
Both functions are created in the main schema (the one that is named by the customer), the search_path is therefore set
tothis schema: 
ALTER FUNCTION function1 SET SEARCH_PATH TO <the schema name that the customer has chosen>
Since the search_path of the function is not renamed when the schema is renamed, I need to know that there is such a
search_path,which I then manually change to the new schema name. 
Would it be possible that there is a variable that designates the schema in which a function is located? Like this, for
example:
ALTER FUNCTION function1 SET SEARCH_PATH TO $FUNCTION_SCHEMA;
Since $FUNCTION_SCHEMA would change to denote the new schema when renaming the schema I wouldn't have to change the
search_pathmanually. 
________________________________________________________
Your E-Mail. Your Cloud. Your Office. eclipso Mail & Cloud. https://www.eclipso.de





Re: Changing a schema's name with function1 calling function2

From
Adrian Klaver
Date:
On 12/21/23 05:47, Wilma Wantren wrote:
> Now as text mail, sorry.
> I accidentally posted the following to the bugs mailing list first. Sorry for this, now as a question here.
> The company I work for used to only use Oracle databases for their program and now also supports PostgreSQL.
> With Postgres, we create a database, a user and a schema, which all have the same name. The name is chosen by the
customers.
> Sometimes, the customers would like to export the data and definitions and import them under a new name.
> This works well by using the -O (no owner) option for pg_dump/pg_restore and only exporting and importing the schema.
Afterthe import the schema is renamed. So far so simple and easy!
 
> But there is one problem: we have a database function function1, which calls another function function2. To ensure
thatthe function call is safe, we set a search_path for function1.
 
> Both functions are created in the main schema (the one that is named by the customer), the search_path is therefore
setto this schema:
 
> ALTER FUNCTION function1 SET SEARCH_PATH TO <the schema name that the customer has chosen>
> Since the search_path of the function is not renamed when the schema is renamed, I need to know that there is such a
search_path,which I then manually change to the new schema name.
 
> Would it be possible that there is a variable that designates the schema in which a function is located? Like this,
forexample:
 
> ALTER FUNCTION function1 SET SEARCH_PATH TO $FUNCTION_SCHEMA;
> Since $FUNCTION_SCHEMA would change to denote the new schema when renaming the schema I wouldn't have to change the
search_pathmanually.
 

From

https://www.postgresql.org/docs/current/sql-alterfunction.html

"
configuration_parameter
value

     Add or change the assignment to be made to a configuration 
parameter when the function is called. If value is DEFAULT or, 
equivalently, RESET is used, the function-local setting is removed, so 
that the function executes with the value present in its environment. 
Use RESET ALL to clear all function-local settings. SET FROM CURRENT 
saves the value of the parameter that is current when ALTER FUNCTION is 
executed as the value to be applied when the function is entered.

     See SET and Chapter 20 for more information about allowed parameter 
names and values.
"

Not sure if that would meet your requirements.

I could see doing in a session:

SET search_path = 'main_schema';

ALTER FUNCTION function SET search_path FROM CURRENT;

> ________________________________________________________
> Your E-Mail. Your Cloud. Your Office. eclipso Mail & Cloud. https://www.eclipso.de
> 
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: Re: Changing a schema's name with function1 calling function2

From
"Wilma Wantren"
Date:
Thank you for your response!
No, this does not help me. I will try to explain better what I mean. Our customers use our program with a Postgres
database,user and schema, which have the same name. The customer chooses the name. Let's say one customer calls the
database,user and schema 'my_things'.  
I want to create scripts for the customers with which they can export the schema and import it into another database
witha new schema name, let's say one customer wants to import it as 'my_things1' (this schema belongs to database
'my_things1').
The export script calls pg_dump to export schema 'my_things'.
The import script calls pg_restore to restore schema 'my_things' in database 'my_things1' and then calls psql to change
theschema name to 'my_things1'. 
Now there is function1 which its search_path set to 'my_things'. Because the search_path is still set to 'my_things'
afterrenaming the schema the script must now call psql to change the function's search_path to 'my_things1'.  
This is not just one line more in the import script.
It is a fact that I must know - if I did not know about function1's search_path then there would be an error in the
schemaafter renaming the schema.  
And imagine if one day a colleague of mine implements a new function which needs a search_path but the colleague
forgetsto adjust the import script then again there is an error in the schema after renaming the schema.  
Therefore it would be great if there was a variable which I could set in a search_path (like the variable "$user")
whichdenotes the function's schema and which is only evaluated when the function is executed, i.e. the variable would
bethe value of the function's search_path in the function's meta data. This variable, e.g. "$function_schema" would
stilldenote the correct schema after renaming the schema. 

--- Ursprüngliche Nachricht ---
Von: Adrian Klaver <adrian.klaver@aklaver.com>
Datum: 21.12.2023 17:30:02
An: Wilma Wantren <wilma.wantren@eclipso.de>,  pgsql-general@lists.postgresql.org
Betreff: Re: Changing a schema's name with function1 calling function2

On 12/21/23 05:47, Wilma Wantren wrote:
> Now as text mail, sorry.
> I accidentally posted the following to the bugs mailing list first.
Sorry for this, now as a question here.
> The company I work for used to only use Oracle databases for their program
and now also supports PostgreSQL.
> With Postgres, we create a database, a user and a schema, which all
have the same name. The name is chosen by the customers.
> Sometimes, the customers would like to export the data and definitions
and import them under a new name.
> This works well by using the -O (no owner) option for pg_dump/pg_restore
and only exporting and importing the schema. After the import the schema
is renamed. So far so simple and easy!
> But there is one problem: we have a database function function1, which
calls another function function2. To ensure that the function call is safe,
we set a search_path for function1.
> Both functions are created in the main schema (the one that is named
by the customer), the search_path is therefore set to this schema:
> ALTER FUNCTION function1 SET SEARCH_PATH TO <the schema name that
the customer has chosen>
> Since the search_path of the function is not renamed when the schema
is renamed, I need to know that there is such a search_path, which I then
manually change to the new schema name.
> Would it be possible that there is a variable that designates the schema
in which a function is located? Like this, for example:
> ALTER FUNCTION function1 SET SEARCH_PATH TO $FUNCTION_SCHEMA;
> Since $FUNCTION_SCHEMA would change to denote the new schema when renaming
the schema I wouldn't have to change the search_path manually.

From

https://www.postgresql.org/docs/current/sql-alterfunction.html

"
configuration_parameter
value

     Add or change the assignment to be made to a configuration
parameter when the function is called. If value is DEFAULT or,
equivalently, RESET is used, the function-local setting is removed, so
that the function executes with the value present in its environment.
Use RESET ALL to clear all function-local settings. SET FROM CURRENT
saves the value of the parameter that is current when ALTER FUNCTION is

executed as the value to be applied when the function is entered.

     See SET and Chapter 20 for more information about allowed parameter

names and values.
"

Not sure if that would meet your requirements.

I could see doing in a session:

SET search_path = 'main_schema';

ALTER FUNCTION function SET search_path FROM CURRENT;

> ________________________________________________________
> Your E-Mail. Your Cloud. Your Office. eclipso Mail & Cloud. https://www.eclipso.de

>
>
>
>

--
Adrian Klaver
adrian.klaver@aklaver.com



________________________________________________________
Your E-Mail. Your Cloud. Your Office. eclipso Mail & Cloud. https://www.eclipso.de





Re: Changing a schema's name with function1 calling function2

From
Adrian Klaver
Date:
On 12/22/23 15:09, Wilma Wantren wrote:
> Thank you for your response!
> No, this does not help me. I will try to explain better what I mean. Our customers use our program with a Postgres
database,user and schema, which have the same name. The customer chooses the name. Let's say one customer calls the
database,user and schema 'my_things'.
 
> I want to create scripts for the customers with which they can export the schema and import it into another database
witha new schema name, let's say one customer wants to import it as 'my_things1' (this schema belongs to database
'my_things1').
> The export script calls pg_dump to export schema 'my_things'.
> The import script calls pg_restore to restore schema 'my_things' in database 'my_things1' and then calls psql to
changethe schema name to 'my_things1'.
 
> Now there is function1 which its search_path set to 'my_things'. Because the search_path is still set to 'my_things'
afterrenaming the schema the script must now call psql to change the function's search_path to 'my_things1'.
 
> This is not just one line more in the import script.
> It is a fact that I must know - if I did not know about function1's search_path then there would be an error in the
schemaafter renaming the schema.
 
> And imagine if one day a colleague of mine implements a new function which needs a search_path but the colleague
forgetsto adjust the import script then again there is an error in the schema after renaming the schema.
 
> Therefore it would be great if there was a variable which I could set in a search_path (like the variable "$user")
whichdenotes the function's schema and which is only evaluated when the function is executed, i.e. the variable would
bethe value of the function's search_path in the function's meta data. This variable, e.g. "$function_schema" would
stilldenote the correct schema after renaming the schema.
 
> 

There is no such variable. What you have available is what I showed before:

https://www.postgresql.org/docs/current/sql-alterfunction.html

"
configuration_parameter
value

      Add or change the assignment to be made to a configuration
parameter when the function is called. If value is DEFAULT or,
equivalently, RESET is used, the function-local setting is removed, so
that the function executes with the value present in its environment.
Use RESET ALL to clear all function-local settings. SET FROM CURRENT
saves the value of the parameter that is current when ALTER FUNCTION is

executed as the value to be applied when the function is entered.

      See SET and Chapter 20 for more information about allowed parameter

names and values.
"

The alternative is to create a migration process using scripts with 
variable substitution outside the database. I do something similar using 
Sqitch(https://sqitch.org/) and its template system:

https://sqitch.org/docs/manual/sqitch-add/

-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: Re: Changing a schema's name with function1 calling function2

From
"Wilma Wantren"
Date:
Thank you!
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
suspectthat 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.  
Many thanks for the reference to sqitch, I'll have a look at that now.


Translated with DeepL.com (free version)
--- Ursprüngliche Nachricht ---
Von: Adrian Klaver <adrian.klaver@aklaver.com>
Datum: 23.12.2023 01:09:12
An: Wilma Wantren <wilma.wantren@eclipso.de>
Betreff: Re: Changing a schema's name with function1 calling function2

On 12/22/23 15:09, Wilma Wantren wrote:
> Thank you for your response!
> No, this does not help me. I will try to explain better what I mean.
Our customers use our program with a Postgres database, user and schema,
which have the same name. The customer chooses the name. Let's say one customer
calls the database, user and schema 'my_things'.
> I want to create scripts for the customers with which they can export
the schema and import it into another database with a new schema name, let's
say one customer wants to import it as 'my_things1' (this schema belongs
to database 'my_things1').
> The export script calls pg_dump to export schema 'my_things'.
> The import script calls pg_restore to restore schema 'my_things' in
database 'my_things1' and then calls psql to change the schema name to 'my_things1'.

> Now there is function1 which its search_path set to 'my_things'. Because
the search_path is still set to 'my_things' after renaming the schema the
script must now call psql to change the function's search_path to 'my_things1'.

> This is not just one line more in the import script.
> It is a fact that I must know - if I did not know about function1's
search_path then there would be an error in the schema after renaming the
schema.
> And imagine if one day a colleague of mine implements a new function
which needs a search_path but the colleague forgets to adjust the import
script then again there is an error in the schema after renaming the schema.

> 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. This variable, e.g. "$function_schema" would
still denote the correct schema after renaming the schema.
>

There is no such variable. What you have available is what I showed before:


https://www.postgresql.org/docs/current/sql-alterfunction.html

"
configuration_parameter
value

      Add or change the assignment to be made to a configuration
parameter when the function is called. If value is DEFAULT or,
equivalently, RESET is used, the function-local setting is removed, so
that the function executes with the value present in its environment.
Use RESET ALL to clear all function-local settings. SET FROM CURRENT
saves the value of the parameter that is current when ALTER FUNCTION is

executed as the value to be applied when the function is entered.

      See SET and Chapter 20 for more information about allowed parameter


names and values.
"

The alternative is to create a migration process using scripts with
variable substitution outside the database. I do something similar using

Sqitch(https://sqitch.org/) and its template system:

https://sqitch.org/docs/manual/sqitch-add/

--
Adrian Klaver
adrian.klaver@aklaver.com



________________________________________________________
Your E-Mail. Your Cloud. Your Office. eclipso Mail & Cloud. https://www.eclipso.de





Re: Changing a schema's name with function1 calling function2

From
Adrian Klaver
Date:
On 12/23/23 08:12, Wilma Wantren wrote:
> Thank you!
> I had already feared that such a variable does not exist (because I had not found it). I think that's a pity, because
Isuspect that in at least 90% of the cases where a function needs a search_path, this variable would be the value of
thesearch_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.

> Many thanks for the reference to sqitch, I'll have a look at that now.
>   
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: Changing a schema's name with function1 calling function2

From
"Peter J. Holzer"
Date:
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!"

Attachment

Re: Changing a schema's name with function1 calling function2

From
Adrian Klaver
Date:
On 12/24/23 13:43, Peter J. Holzer wrote:
> On 2023-12-23 08:31:39 -0800, Adrian Klaver wrote:

> 
> 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.

Maybe, but I don't think so. From one of Wilma's 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. This variable, e.g. 
"$function_schema" would still denote the correct schema after renaming 
the schema."

It seems to be pretty tied to search_path. Now there is, from:

https://www.postgresql.org/docs/current/catalog-pg-proc.html

proconfig text[]

Function's local settings for run-time configuration variables

Though that is part of the function definition not the code inside the 
function as you show below. Also you would still need to determine what 
was the appropriate schema before creating/altering the setting for the 
function.

There is also:

pronamespace oid (references pg_namespace.oid)

The OID of the namespace that contains this function

which could be construed as '... the function meta data.', though its 
not strictly search_path. I could see that being part of some dynamic 
SQL to set the search_path.


> 
> 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.

That assumes the first schema is the correct one. Not something that 
could be counted on given search_path can be set any number of ways at 
any time.

> 
> 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
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: Changing a schema's name with function1 calling function2

From
"Peter J. Holzer"
Date:
On 2023-12-24 14:27:19 -0800, Adrian Klaver wrote:
> On 12/24/23 13:43, Peter J. Holzer wrote:
> > 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.
>
> Maybe, but I don't think so. From one of Wilma's 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. This variable, e.g. "$function_schema" would still denote the correct
> schema after renaming the schema."

I interpreted that as meaning what I wrote above.

So we'll have to wait for Wilma to clarify what she really meant.


> It seems to be pretty tied to search_path. Now there is, from:
>
> https://www.postgresql.org/docs/current/catalog-pg-proc.html
>
> proconfig text[]
>
> Function's local settings for run-time configuration variables
>
> Though that is part of the function definition not the code inside the
> function as you show below. Also you would still need to determine what was
> the appropriate schema before creating/altering the setting for the
> function.

"Determining the appropriate schema" is what - AIUI - the requested
magic variable is for.


> > 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.
>
> That assumes the first schema is the correct one. Not something that could
> be counted on given search_path can be set any number of ways at any time.

Yes, I was sloppy there. What I actually meant was "the first schema
where the user actually has permission to create a function" (and no
doubt that isn't 100% correct either). I would expect that in a
deployment situation that would be the first schema in the search_path,
but you are of course correct that this is not necessarily the case.
Anyway, that was only illustrative. The point of my examples was that no
matter how the function is created, __function_schema__ always refers to
the schema the function actually is in.

        hp


--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachment

Re: Re: Changing a schema's name with function1 calling function2

From
"Wilma Wantren"
Date:
Great that you have discussed this further! And sorry that what I wrote is apparently still misleading.
Peter understood me correctly, "__function_schema__ always refers to the schema the function actually is in".
I define:
1. create function my_schema.function1...
2. alter function function1 set search_path to __function_schema__
The variable __function_schema__ is not evaluated, but is set "as it is" in the metadata of the function, which
thereforereads:  
...
search_path: __function_schema__
...
Only when function1 is executed the variable is evaluated, namely "to the schema the function actually is in", in this
caseto 'my_schema'. If the schema is renamed, the search_path of function1 does not have to be changed, and is
neverthelessevaluated correctly again when the function is executed, now to the new schema name. 

--- Ursprüngliche Nachricht ---
Von: "Peter J. Holzer" <hjp-pgsql@hjp.at>
Datum: 25.12.2023 15:37:26
An: Adrian Klaver <adrian.klaver@aklaver.com>
Betreff: Re: Changing a schema's name with function1 calling function2

On 2023-12-24 14:27:19 -0800, Adrian Klaver wrote:
> On 12/24/23 13:43, Peter J. Holzer wrote:
> > 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.
>
> Maybe, but I don't think so. From one of Wilma's 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. This variable, e.g. "$function_schema" would still denote
the correct
> schema after renaming the schema."

I interpreted that as meaning what I wrote above.

So we'll have to wait for Wilma to clarify what she really meant.


> It seems to be pretty tied to search_path. Now there is, from:
>
> https://www.postgresql.org/docs/current/catalog-pg-proc.html
>
> proconfig text[]
>
> Function's local settings for run-time configuration variables
>
> Though that is part of the function definition not the code inside the

> function as you show below. Also you would still need to determine what
was
> the appropriate schema before creating/altering the setting for the

> function.

"Determining the appropriate schema" is what - AIUI - the requested

magic variable is for.


> > 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.
>
> That assumes the first schema is the correct one. Not something that
could
> be counted on given search_path can be set any number of ways at any
time.

Yes, I was sloppy there. What I actually meant was "the first schema

where the user actually has permission to create a function" (and no

doubt that isn't 100% correct either). I would expect that in a
deployment situation that would be the first schema in the search_path,
but you are of course correct that this is not necessarily the case.
Anyway, that was only illustrative. The point of my examples was that no

matter how the function is created, __function_schema__ always refers to

the schema the function actually is in.

        hp


--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing

__/   | http://www.hjp.at/ |       challenge!"


________________________________________________________
Your E-Mail. Your Cloud. Your Office. eclipso Mail & Cloud. https://www.eclipso.de





Re: Changing a schema's name with function1 calling function2

From
Adrian Klaver
Date:
On 12/25/23 09:29, Wilma Wantren wrote:
> Great that you have discussed this further! And sorry that what I wrote is apparently still misleading.
> Peter understood me correctly, "__function_schema__ always refers to the schema the function actually is in".
> I define:
> 1. create function my_schema.function1...
> 2. alter function function1 set search_path to __function_schema__
> The variable __function_schema__ is not evaluated, but is set "as it is" in the metadata of the function, which
thereforereads:
 
> ...
> search_path: __function_schema__
> ...
> Only when function1 is executed the variable is evaluated, namely "to the schema the function actually is in", in
thiscase to 'my_schema'. If the schema is renamed, the search_path of function1 does not have to be changed, and is
neverthelessevaluated correctly again when the function is executed, now to the new schema name.
 

Alright I see now.

A simple example of a partial solution:


CREATE OR REPLACE FUNCTION public.schema_test()
  RETURNS void
  LANGUAGE plpgsql
AS $function$
DECLARE
     fnc_name  varchar := 'schema_test';
     sch_name  varchar;
     cur_sch   name;
BEGIN
     SELECT INTO sch_name pronamespace::regnamespace from pg_proc where 
proname = fnc_name;
     RAISE NOTICE 'Schema is %', sch_name;
     PERFORM set_config('search_path', sch_name, true);
     select into cur_sch current_schema;
     RAISE NOTICE 'search_path = %', cur_sch;

END;

Issues:

fnc_name would need to kept updated.
Overloaded function names would need to be accounted for.
Functions with same name in different schemas would need to be dealt with.


At this point I still the solution as being external to the database. In 
other words making the change before the objects are loaded into a database.

-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: Changing a schema's name with function1 calling function2

From
Christophe Pettus
Date:

> On Dec 25, 2023, at 10:44, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> Functions with same name in different schemas would need to be dealt with.

I think that's the primary use-case (at least, it would be for me), and I don't see a convenient way of doing that.
Evena "get OID of current function" function would be useful here. 


Re: Changing a schema's name with function1 calling function2

From
Kirk Wolak
Date:
On Mon, Dec 25, 2023 at 1:47 PM Christophe Pettus <xof@thebuild.com> wrote:


> On Dec 25, 2023, at 10:44, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> Functions with same name in different schemas would need to be dealt with.

I think that's the primary use-case (at least, it would be for me), and I don't see a convenient way of doing that.  Even a "get OID of current function" function would be useful here.

And Pavel Already Created it:
GET DIAGNOSTIC PID = PG_ROUTINE_OID ;

If I understand the issue correctly.

Kirk Out!

Re: Changing a schema's name with function1 calling function2

From
Adrian Klaver
Date:
On 12/26/23 22:41, Kirk Wolak wrote:
> On Mon, Dec 25, 2023 at 1:47 PM Christophe Pettus <xof@thebuild.com 
> <mailto:xof@thebuild.com>> wrote:
> 
> 
> 
>      > On Dec 25, 2023, at 10:44, Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>      > Functions with same name in different schemas would need to be
>     dealt with.
> 
>     I think that's the primary use-case (at least, it would be for me),
>     and I don't see a convenient way of doing that.  Even a "get OID of
>     current function" function would be useful here.
> 
> And Pavel Already Created it:
> GET DIAGNOSTIC PID = PG_ROUTINE_OID ;

To be clear the above is for Postgres 16+ and for the plpgsql language only.

> 
> If I understand the issue correctly.
> 
> Kirk Out!

-- 
Adrian Klaver
adrian.klaver@aklaver.com




Re: Re: Changing a schema's name with function1 calling function2

From
"Wilma Wantren"
Date:
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. 
Instead, I should now call the - actually diagnostic - function PG_ROUTINE_OID from the body of my function, with which
Iget the OID of my function in order to then determine the schema of my function and set it as search_path. I don't
thinkthat 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


--- Ursprüngliche Nachricht ---
Von: Adrian Klaver <adrian.klaver@aklaver.com>
Datum: 27.12.2023 16:40:55
An: Kirk Wolak <wolakk@gmail.com>, Christophe Pettus <xof@thebuild.com>
Betreff: Re: Changing a schema's name with function1 calling function2

On 12/26/23 22:41, Kirk Wolak wrote:
> On Mon, Dec 25, 2023 at 1:47 PM Christophe Pettus <xof@thebuild.com

> <mailto:xof@thebuild.com>> wrote:
>
>
>
>      > On Dec 25, 2023, at 10:44, Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
wrote:
>      > Functions with same name in different schemas would need to
be
>     dealt with.
>
>     I think that's the primary use-case (at least, it would be for me),

>     and I don't see a convenient way of doing that.  Even a "get
OID of
>     current function" function would be useful here.
>
> And Pavel Already Created it:
> GET DIAGNOSTIC PID = PG_ROUTINE_OID ;

To be clear the above is for Postgres 16+ and for the plpgsql language only.


>
> If I understand the issue correctly.
>
> Kirk Out!

--
Adrian Klaver
adrian.klaver@aklaver.com



________________________________________________________
Your E-Mail. Your Cloud. Your Office. eclipso Mail Europe. https://www.eclipso.de





Re: Changing a schema's name with function1 calling function2

From
Adrian Klaver
Date:
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




Re: Re: Changing a schema's name with function1 calling function2

From
"Wilma Wantren"
Date:
That's really nice of you to point me to this mailing list! I'll make my suggestion there in the next few days.
I don't think it's so bad if the new feature is not available until fall 2024 or even later, the time in which it's
usefulwould be very long in any case. 
Happy New Year to you and thanks again
Wilma

--- Ursprüngliche Nachricht ---
Von: Adrian Klaver <adrian.klaver@aklaver.com>
Datum: 30.12.2023 19:05:28
An: Wilma Wantren <wilma.wantren@eclipso.de>
Betreff: Re: Changing a schema's name with function1 calling function2

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 schema the 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 determine and cache the variable value (i.e.
the schema of the function) directly when setting the search_path, and to
redetermine and 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 which I get the OID of my function in
order to then determine the schema of my function and set it as search_path.
I don't think that suits my requirements.
>
> I will therefore consider using a database change management system
instead (e.g. sqitch, suggested by Adrian) and defining there what should
happen when the schema name is changed, including the names of all functions
whose search_path is to be changed.
>
> Many thanks again
> Wilma
>

--
Adrian Klaver
adrian.klaver@aklaver.com



________________________________________________________
Your E-Mail. Your Cloud. Your Office. eclipso Mail Europe. https://www.eclipso.de