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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
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@xxxxxxxxxxx
