pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joel Rabinovitch <Joel.Rabinovitch@tecsys.com>
To: David G. Johnston <david.g.johnston@gmail.com>
To: Lorusso Domenico <domenico.l76@gmail.com>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>
Subject: RE: How to add function schema in search_path in option definitio
Date: Wed, 12 Jul 2023 13:28:48 +0000
Message-ID: <YT2PR01MB88057F34ABA056ADA62FDA89E536A@YT2PR01MB8805.CANPRD01.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <CAKFQuwZo8B9PMdBL2o7Tzr9HuDfobBk5XpPWrsuf4Lv44uP3Vw@mail.gmail.com>
References: <CAJMpnG7LqoS5=2derJ3UmL1UGw859RtjZ1buM1EMa+CL+8mjfQ@mail.gmail.com>
	<9f3db764-804e-7154-3f8c-f5f19628178e@aklaver.com>
	<CAJMpnG5AnsPa6hBsz3g4V80rG1Abvt9CrZwN9QHGOpHavRo=BA@mail.gmail.com>
	<CAKFQuwZo8B9PMdBL2o7Tzr9HuDfobBk5XpPWrsuf4Lv44uP3Vw@mail.gmail.com>

Hi,

We have hit similar issues with the software that we develop. We don’t specify the schema names in stored procedures/functions we create.

The way we resolved it was to define the schemas where the stored procedures/functions are located in the search path as part of the connection string we use. In our case, we connect using JDBC, so the search path is defined in the currentSchema argument.

We needed to do this because one of the things our installation teams do is copy schemas used in one environment, such as a production environment, to another environment,  such as a test environment. When that is done, the schemas are renamed as per our installation standards. If we hardcoded the schema names in the stored procedures/functions, the installer would have to manually adjust the schema names used.

Thanks,

Joel

From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Tuesday, July 11, 2023 4:51 PM
To: Lorusso Domenico <domenico.l76@gmail.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org
Subject: Re: How to add function schema in search_path in option definitio

ATTENTION: This email originated from outside of Tecsys. Use caution when clicking links or opening attachments. | Ce courriel provient de l'extérieur de Tecsys. Soyez prudent lorsque vous cliquez sur des liens ou ouvrez des pièces jointes.

On Sat, Jul 8, 2023 at 10:00 AM Lorusso Domenico <domenico.l76@gmail.com<mailto:domenico.l76@gmail.com>> wrote:
Hello Adrian,
I've created a schema to handle some specific features.
In the schema there are a couple of tables used by many functions (more than 20).
In other words, I've created a schema as a package (as suggested in many points).

I wish, in a function of this schema, to be able to call each other functions of this schema without adding the schema name in the call instruction.

PostgreSQL isn't really designed for that - especially if you aren't formally creating an extension but simply putting stuff into a schema.

The script code you use to install your makeshift package should handle dynamic schema naming.  It's a pain, do you really need to allow the name of the schema to be unknown at installation time?

You should read up on how extensions are implemented - you are basically writing your own CREATE EXTENSION implementation.

https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-RELOCATION

In any case, there really aren't any smarts here: explicitly schema qualify your function calls and forget that search_path even exists.  Unless you are writing custom operators, and even then, consider search_path to be evil.

David J.



view thread (15+ messages)  latest in thread

Message-ID: <YT2PR01MB88057F34ABA056ADA62FDA89E536A@YT2PR01MB8805.CANPRD01.PROD.OUTLOOK.COM>
Permalink:  ../YT2PR01MB88057F34ABA056ADA62FDA89E536A@YT2PR01MB8805.CANPRD01.PROD.OUTLOOK.COM/
Also on:    postgresql.org/message-id/YT2PR01MB88057F34ABA056ADA62FDA89E536A@YT2PR01MB8805.CANPRD01.PROD.OUTLOOK.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: Joel.Rabinovitch@tecsys.com, david.g.johnston@gmail.com, domenico.l76@gmail.com, adrian.klaver@aklaver.com
  Subject: Re: How to add function schema in search_path in option definitio
  In-Reply-To: <YT2PR01MB88057F34ABA056ADA62FDA89E536A@YT2PR01MB8805.CANPRD01.PROD.OUTLOOK.COM>
  References: <CAJMpnG7LqoS5=2derJ3UmL1UGw859RtjZ1buM1EMa+CL+8mjfQ@mail.gmail.com> <9f3db764-804e-7154-3f8c-f5f19628178e@aklaver.com> <CAJMpnG5AnsPa6hBsz3g4V80rG1Abvt9CrZwN9QHGOpHavRo=BA@mail.gmail.com> <CAKFQuwZo8B9PMdBL2o7Tzr9HuDfobBk5XpPWrsuf4Lv44uP3Vw@mail.gmail.com> <YT2PR01MB88057F34ABA056ADA62FDA89E536A@YT2PR01MB8805.CANPRD01.PROD.OUTLOOK.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