pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to add function schema in search_path in option definitio
15+ messages / 5 participants
[nested] [flat]
* How to add function schema in search_path in option definitio
@ 2023-07-08 15:06 Lorusso Domenico <domenico.l76@gmail.com>
0 siblings, 2 replies; 15+ messages in thread
From: Lorusso Domenico @ 2023-07-08 15:06 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
--
Domenico L.
per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to add function schema in search_path in option definitio
@ 2023-07-08 15:31 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Lorusso Domenico <domenico.l76@gmail.com>
1 sibling, 0 replies; 15+ messages in thread
From: Adrian Klaver @ 2023-07-08 15:31 UTC (permalink / raw)
To: Lorusso Domenico <domenico.l76@gmail.com>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to add function schema in search_path in option definitio
@ 2023-07-08 15:46 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Lorusso Domenico <domenico.l76@gmail.com>
1 sibling, 1 reply; 15+ messages in thread
From: Adrian Klaver @ 2023-07-08 15:46 UTC (permalink / raw)
To: Lorusso Domenico <domenico.l76@gmail.com>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to add function schema in search_path in option definitio
@ 2023-07-08 17:00 Lorusso Domenico <domenico.l76@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 3 replies; 15+ messages in thread
From: Lorusso Domenico @ 2023-07-08 17:00 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org
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);
Il giorno sab 8 lug 2023 alle ore 17:46 Adrian Klaver <
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
>
>
--
Domenico L.
per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to add function schema in search_path in option definitio
@ 2023-07-08 21:10 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Lorusso Domenico <domenico.l76@gmail.com>
2 siblings, 0 replies; 15+ messages in thread
From: Adrian Klaver @ 2023-07-08 21:10 UTC (permalink / raw)
To: Lorusso Domenico <domenico.l76@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to add function schema in search_path in option definitio
@ 2023-07-09 11:01 Peter J. Holzer <hjp-pgsql@hjp.at>
parent: Lorusso Domenico <domenico.l76@gmail.com>
2 siblings, 1 reply; 15+ messages in thread
From: Peter J. Holzer @ 2023-07-09 11:01 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to add function schema in search_path in option definitio
@ 2023-07-09 20:47 Lorusso Domenico <domenico.l76@gmail.com>
parent: Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 1 reply; 15+ messages in thread
From: Lorusso Domenico @ 2023-07-09 20:47 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
Il giorno dom 9 lug 2023 alle ore 13:02 Peter J. Holzer <hjp-pgsql@hjp.at>
ha scritto:
> 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!"
>
--
Domenico L.
per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to add function schema in search_path in option definitio
@ 2023-07-09 21:21 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Lorusso Domenico <domenico.l76@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Adrian Klaver @ 2023-07-09 21:21 UTC (permalink / raw)
To: Lorusso Domenico <domenico.l76@gmail.com>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to add function schema in search_path in option definitio
@ 2023-07-11 19:35 Lorusso Domenico <domenico.l76@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 15+ messages in thread
From: Lorusso Domenico @ 2023-07-11 19:35 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org
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 :-)
Il giorno dom 9 lug 2023 alle ore 23:21 Adrian Klaver <
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
>
> showed a way to do that.
>
> --
> Adrian Klaver
> 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.]
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to add function schema in search_path in option definitio
@ 2023-07-11 19:41 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Lorusso Domenico <domenico.l76@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Adrian Klaver @ 2023-07-11 19:41 UTC (permalink / raw)
To: Lorusso Domenico <domenico.l76@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to add function schema in search_path in option definitio
@ 2023-07-11 19:47 Lorusso Domenico <domenico.l76@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 15+ messages in thread
From: Lorusso Domenico @ 2023-07-11 19:47 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org
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
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.
Il giorno mar 11 lug 2023 alle ore 21:41 Adrian Klaver <
adrian.klaver@aklaver.com> ha scritto:
> 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
>
>
--
Domenico L.
per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to add function schema in search_path in option definitio
@ 2023-07-11 20:17 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Lorusso Domenico <domenico.l76@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Adrian Klaver @ 2023-07-11 20:17 UTC (permalink / raw)
To: Lorusso Domenico <domenico.l76@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to add function schema in search_path in option definitio
@ 2023-07-11 20:50 David G. Johnston <david.g.johnston@gmail.com>
parent: Lorusso Domenico <domenico.l76@gmail.com>
2 siblings, 2 replies; 15+ messages in thread
From: David G. Johnston @ 2023-07-11 20:50 UTC (permalink / raw)
To: Lorusso Domenico <domenico.l76@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org
On Sat, Jul 8, 2023 at 10:00 AM Lorusso Domenico <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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: How to add function schema in search_path in option definitio
@ 2023-07-12 13:28 Joel Rabinovitch <Joel.Rabinovitch@tecsys.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 15+ messages in thread
From: Joel Rabinovitch @ 2023-07-12 13:28 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; Lorusso Domenico <domenico.l76@gmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>; +Cc: Adrian Klaver <adrian.klaver@aklaver.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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to add function schema in search_path in option definitio
@ 2023-07-12 20:45 Lorusso Domenico <domenico.l76@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 15+ messages in thread
From: Lorusso Domenico @ 2023-07-12 20:45 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org
eh.. yes to be honest what I create is more similar to an extension rather
than a package, I don't believe to be able to create extensions in cloud
sql (the actual target db).
However , is there a way to share this preliminar "extension" with the
community?
I'm interested to discuss about solution used
For example: the software creates a custom type to manage bitemporality and
audit_record inline for each table; but another approach is to use a
primitive table inherited by all tables.
I'm quite sure the composite type has one or two fields I can remove.
etc :-D
Il giorno mar 11 lug 2023 alle ore 22:51 David G. Johnston <
david.g.johnston@gmail.com> ha scritto:
> On Sat, Jul 8, 2023 at 10:00 AM Lorusso Domenico <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.
>
>
--
Domenico L.
per stupire mezz'ora basta un libro di storia,
io cercai di imparare la Treccani a memoria... [F.d.A.]
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2023-07-12 20:45 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-08 15:06 How to add function schema in search_path in option definitio Lorusso Domenico <domenico.l76@gmail.com>
2023-07-08 15:31 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-08 15:46 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-08 17:00 ` Lorusso Domenico <domenico.l76@gmail.com>
2023-07-08 21:10 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-09 11:01 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-07-09 20:47 ` Lorusso Domenico <domenico.l76@gmail.com>
2023-07-09 21:21 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-11 19:35 ` Lorusso Domenico <domenico.l76@gmail.com>
2023-07-11 19:41 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-11 19:47 ` Lorusso Domenico <domenico.l76@gmail.com>
2023-07-11 20:17 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-07-11 20:50 ` David G. Johnston <david.g.johnston@gmail.com>
2023-07-12 13:28 ` Joel Rabinovitch <Joel.Rabinovitch@tecsys.com>
2023-07-12 20:45 ` Lorusso Domenico <domenico.l76@gmail.com>
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