RE: How to add function schema in search_path in option definitio

[Joel Rabinovitch <Joel.Rabinovitch@xxxxxxxxxx>]

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@xxxxxxxxx>
Sent: Tuesday, July 11, 2023 4:51 PM
To: Lorusso Domenico <domenico.l76@xxxxxxxxx>
Cc: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxx> 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.