pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Wilma Wantren <wilma.wantren@eclipso.de>
To: Peter J. Holzer <hjp-pgsql@hjp.at>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Changing a schema's name with function1 calling function2
Date: Mon, 25 Dec 2023 10:44:04 -0800
Message-ID: <b89c4a7f-2663-44d7-8a4e-07fa657b5b29@aklaver.com> (raw)
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>

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

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






view thread (18+ messages)  latest in thread

Message-ID: <b89c4a7f-2663-44d7-8a4e-07fa657b5b29@aklaver.com>
Permalink:  ../b89c4a7f-2663-44d7-8a4e-07fa657b5b29@aklaver.com/
Also on:    postgresql.org/message-id/b89c4a7f-2663-44d7-8a4e-07fa657b5b29@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, wilma.wantren@eclipso.de, hjp-pgsql@hjp.at
  Subject: Re: Changing a schema's name with function1 calling function2
  In-Reply-To: <b89c4a7f-2663-44d7-8a4e-07fa657b5b29@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> <20231225143726.gfynym24xqcc27tu@hjp.at> <9bb4fd9a3e59adce379c3828e2a73646@mail.eclipso.de> <b89c4a7f-2663-44d7-8a4e-07fa657b5b29@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