pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Wilma Wantren <wilma.wantren@eclipso.de>
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: wolakk@gmail.com
Cc: xof@thebuild.com
Cc: hjp-pgsql@hjp.at
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Re: Changing a schema's name with function1 calling function2
Date: Sat, 30 Dec 2023 17:01:57 +0100
Message-ID: <9402749f7bc1ec4fb28989f72aa50676@mail.eclipso.de> (raw)
In-Reply-To: <c2954d6e-a739-431e-829a-02919d58b48b@aklaver.com>
References: <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>
	<924F91AE-0597-45EE-B8ED-D857EFDCFB09@thebuild.com>
	<CACLU5mRBrLfrBg8RcfqefXutus7Vzfg+DG10Ci64hXNN7eqRuw@mail.gmail.com>
	<c2954d6e-a739-431e-829a-02919d58b48b@aklaver.com>

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


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







view thread (18+ messages)  latest in thread

Message-ID: <9402749f7bc1ec4fb28989f72aa50676@mail.eclipso.de>
Permalink:  ../9402749f7bc1ec4fb28989f72aa50676@mail.eclipso.de/
Also on:    postgresql.org/message-id/9402749f7bc1ec4fb28989f72aa50676@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, adrian.klaver@aklaver.com, wolakk@gmail.com, xof@thebuild.com, hjp-pgsql@hjp.at
  Subject: Re: Changing a schema's name with function1 calling function2
  In-Reply-To: <9402749f7bc1ec4fb28989f72aa50676@mail.eclipso.de>
  References: <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> <924F91AE-0597-45EE-B8ED-D857EFDCFB09@thebuild.com> <CACLU5mRBrLfrBg8RcfqefXutus7Vzfg+DG10Ci64hXNN7eqRuw@mail.gmail.com> <c2954d6e-a739-431e-829a-02919d58b48b@aklaver.com> <9402749f7bc1ec4fb28989f72aa50676@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