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

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

1) Do you want this to happen inside function per:

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

Writing SECURITY DEFINER Functions Safely

-- Set a secure search_path: trusted schema(s), then 'pg_temp'.
     SET search_path = admin, pg_temp;

or outside the function?

2) How long do you want this change to last?

3) Is this 'function_schema()' actually a function and if so what is it 
doing/returning?

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

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

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

On 2023-07-08 19:00:02 +0200, Lorusso Domenico wrote:
> 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).

So this is one specific schema with a specific name and you don't need
this to work in any schema the function happens to be created in?

Then I think setting the search path as part of the function definition
(as already hinted at by Adrian) may be the easiest solution:

create function my_schema.foo (...)
    returns ...
    set search_path to my_schema, public
    as $$
        ...
    $$;

You could also do something like:

set search_path to my_schema, public;
create function foo (...)
    returns ...
    set search_path from current
    as $$
        ...
    $$;

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On 7/9/23 13:47, Lorusso Domenico wrote:
> Hello Adrian and Peter,
> yes the set parameter in function definition is also my preferred 
> choice, but....
> 
> I need to add the schema to path, not to substitute the path, this is my 
> problem
> 

This post:

https://www.postgresql.org/message-id/9f3db764-804e-7154-3f8c-f5f19628178e%40aklaver.com

showed a way to do that.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 7/11/23 12:35, Lorusso Domenico wrote:
> yes, but this is a code inside each function.
>   having 20 functions I've to start executing this instruction 20 times, 
> one per function.
> I know it could work, I looking for something of more smart :-)

Here:

https://www.postgresql.org/message-id/66677cb3-17b3-20d1-1c22-9a7ac3208921%40aklaver.com

I provide options 1) and 3) to use instead of including the path change 
in the functions(s).

> 
> Il giorno dom 9 lug 2023 alle ore 23:21 Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> ha scritto:
> 
>     On 7/9/23 13:47, Lorusso Domenico wrote:
>      > Hello Adrian and Peter,
>      > yes the set parameter in function definition is also my preferred
>      > choice, but....
>      >
>      > I need to add the schema to path, not to substitute the path,
>     this is my
>      > problem
>      >
> 
>     This post:
> 
>     https://www.postgresql.org/message-id/9f3db764-804e-7154-3f8c-f5f19628178e%40aklaver.com
<https://www.postgresql.org/message-id/9f3db764-804e-7154-3f8c-f5f19628178e%40aklaver.com>
> 
>     showed a way to do that.
> 
>     -- 
>     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

On 7/11/23 12:47, Lorusso Domenico wrote:
> Adrian I have read your suggestions, but:
> 1) I need to change path inside a group of function (like a package) not 
> for any connection
> 2) Yes inside function is clear I can do that
> 3) some like 1 and, moreover, I can't be sure to create my own role, 
> because the target db will be cloud sql

First mention that you are using a fork of the community Postgres.

In any case Cloud SQL does allow creating roles:

https://cloud.google.com/sql/docs/postgres/users

My guess though is you will need to do 2).

> 
> My question doesn't aim just to fix a specific issue, but to learn the 
> best way (if exist) to solve the class of issues (raised by the case) in 
> postgresql.
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com