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

On 7/8/23 10:00, Lorusso Domenico 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.
> 
> For example:
> in schema my_schema there are function foo and bar.
> 
> in foo I need to call bar:
> foo as $$
> begin
>      [...]
>      _returnOfBar=my_schema.bar(p1,p2,..,pn);
>      [...]
> end;
> $$
> 
> I'd like to use just _returnOfBar=bar(p1,p2,..,pn);

I see three choices:

1) On connection do

select set_config('search_path', 'test, ' || 
current_setting('search_path'), 'f');

with 't' to restrict to the transaction or with 'f' to restrict to session.

2) Include the search_path reset inside each function.

Or use the SET configuration_parameter { TO value | = value | FROM 
CURRENT } to CREATE FUNCTION per:

https://www.postgresql.org/docs/current/sql-createfunction.html

3) If all this is being done by one role and for this purpose only then 
from here:

https://www.postgresql.org/docs/current/sql-alterrole.html

ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] 
SET configuration_parameter { TO | = } { value | DEFAULT }

Be sure and read the complete docs for the caveats.

> 
> 
> 
> 
> 
> 
> 
> 
> 
> Il giorno sab 8 lug 2023 alle ore 17:46 Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> ha scritto:
> 
>     On 7/8/23 08:06, Lorusso Domenico wrote:
>      > Hello guys,
>      > there is a comfortable way to add the schema of the function as in
>      > search path?
>      >
>      > something like
>      > create my_schema.function() as $body$...$body$
>      > set search_path to function_schema() || search_path
> 
>     You can do something like:
> 
> 
>     show  search_path ;
>          search_path
>     -----------------
>        public, history
> 
> 
>     select set_config('search_path', 'test, ' ||
>     current_setting('search_path'), 'f');
>             set_config
>     -----------------------
>        test, public, history
> 
>     show  search_path ;
>             search_path
>     -----------------------
>        test, public, history
> 
>      >
>      > --
>      > Domenico L.
>      >
>      > per stupire mezz'ora basta un libro di storia,
>      > io cercai di imparare la Treccani a memoria... [F.d.A.]
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
> 
> 
> -- 
> Domenico L.
> 
> per stupire mezz'ora basta un libro di storia,
> io cercai di imparare la Treccani a memoria... [F.d.A.]

-- 
Adrian Klaver
adrian.klaver@aklaver.com