pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Wilma Wantren <wilma.wantren@eclipso.de>
To: Peter J. Holzer <hjp-pgsql@hjp.at>
Cc: adrian.klaver@aklaver.com
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Re: Changing a schema's name with function1 calling function2
Date: Mon, 25 Dec 2023 18:29:32 +0100
Message-ID: <9bb4fd9a3e59adce379c3828e2a73646@mail.eclipso.de> (raw)
In-Reply-To: <20231225143726.gfynym24xqcc27tu@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>
	<9ed20788-82cd-4eba-a590-7a474ca570ef@aklaver.com>
	<20231225143726.gfynym24xqcc27tu@hjp.at>

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 therefore reads: 
...
search_path: __function_schema__
...
Only when function1 is executed the variable is evaluated, namely "to the schema the function actually is in", in this case to 'my_schema'. If the schema is renamed, the search_path of function1 does not have to be changed, and is nevertheless evaluated 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







view thread (18+ messages)  latest in thread

Message-ID: <9bb4fd9a3e59adce379c3828e2a73646@mail.eclipso.de>
Permalink:  ../9bb4fd9a3e59adce379c3828e2a73646@mail.eclipso.de/
Also on:    postgresql.org/message-id/9bb4fd9a3e59adce379c3828e2a73646@mail.eclipso.de

 · 

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: wilma.wantren@eclipso.de, hjp-pgsql@hjp.at, adrian.klaver@aklaver.com
  Subject: Re: Changing a schema's name with function1 calling function2
  In-Reply-To: <9bb4fd9a3e59adce379c3828e2a73646@mail.eclipso.de>
  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> <20231225143726.gfynym24xqcc27tu@hjp.at> <9bb4fd9a3e59adce379c3828e2a73646@mail.eclipso.de>

* 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