pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Changing a schema's name with function1 calling function2
Date: Sun, 24 Dec 2023 14:27:19 -0800
Message-ID: <9ed20788-82cd-4eba-a590-7a474ca570ef@aklaver.com> (raw)
In-Reply-To: <20231224214332.rokhqwkq2xewzobk@hjp.at>
References: <9ed1070f8561f048e72c3a3683565d97@mail.eclipso.de>
	<07aac10034aaf87697ae401475f48909@mail.eclipso.de>
	<aa3e044d-f688-4f91-b6c4-570c0cec96f1@aklaver.com>
	<4cb2e7242b54e84f9d0c161cc56f38e4@mail.eclipso.de>
	<f09b9fb9-98b9-4d39-a47b-c32ca3ff8782@aklaver.com>
	<09a7102b697d2b2acd81cb12344b0170@mail.eclipso.de>
	<90ca8707-6f44-47c2-b781-0138947d7483@aklaver.com>
	<20231224214332.rokhqwkq2xewzobk@hjp.at>

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






view thread (18+ messages)  latest in thread

Message-ID: <9ed20788-82cd-4eba-a590-7a474ca570ef@aklaver.com>
Permalink:  ../9ed20788-82cd-4eba-a590-7a474ca570ef@aklaver.com/
Also on:    postgresql.org/message-id/9ed20788-82cd-4eba-a590-7a474ca570ef@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com
  Subject: Re: Changing a schema's name with function1 calling function2
  In-Reply-To: <9ed20788-82cd-4eba-a590-7a474ca570ef@aklaver.com>
  References: <9ed1070f8561f048e72c3a3683565d97@mail.eclipso.de> <07aac10034aaf87697ae401475f48909@mail.eclipso.de> <aa3e044d-f688-4f91-b6c4-570c0cec96f1@aklaver.com> <4cb2e7242b54e84f9d0c161cc56f38e4@mail.eclipso.de> <f09b9fb9-98b9-4d39-a47b-c32ca3ff8782@aklaver.com> <09a7102b697d2b2acd81cb12344b0170@mail.eclipso.de> <90ca8707-6f44-47c2-b781-0138947d7483@aklaver.com> <20231224214332.rokhqwkq2xewzobk@hjp.at> <9ed20788-82cd-4eba-a590-7a474ca570ef@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox