Обсуждение: PostgreSQL include directive in plpgsql language PL/pgSQL
Hello,
In a PL/pgSQL function, there is no command for sharing a common part of the body of several functions, is there?
In my case, I would like a function that returns a numeric value; this value is associated with several other parameters, some numeric and others textual.
It would not be practical for maintenance to have several functions (one function for each given parameter) with much of the code being identical.
Presently, I have a text function with all the parameters in return and a function for each parameter based on the first one (with all the parameters), using substr(), position(), etc.
For me, I think it would be easier (and perhaps faster) to use some kind of #include in the body of all the functions.
Is there an equivalent of #include (include directive) in functions (written in plpgsql language)?
Regards
In a PL/pgSQL function, there is no command for sharing a common part of the body of several functions, is there?
In my case, I would like a function that returns a numeric value; this value is associated with several other parameters, some numeric and others textual.
It would not be practical for maintenance to have several functions (one function for each given parameter) with much of the code being identical.
Presently, I have a text function with all the parameters in return and a function for each parameter based on the first one (with all the parameters), using substr(), position(), etc.
For me, I think it would be easier (and perhaps faster) to use some kind of #include in the body of all the functions.
Is there an equivalent of #include (include directive) in functions (written in plpgsql language)?
Regards
----- Météo-France -----
PALAYRET Jacques
PALAYRET Jacques
On Friday, September 5, 2025, PALAYRET Jacques <jacques.palayret@meteo.fr> wrote:
Is there an equivalent of #include (include directive) in functions (written in plpgsql language)?
No, there is not.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Friday, September 5, 2025, PALAYRET Jacques <jacques.palayret@meteo.fr>
> wrote:
>> Is there an equivalent of #include (include directive) in functions
>> (written in plpgsql language)?
> No, there is not.
And there won't be one in the future either, as it would
give server filesystem access to unprivileged SQL users.
(Admittedly just read-only access, but that's still not OK.)
Conceivably you could build some such facility into your
client-side code that is issuing CREATE FUNCTION commands.
regards, tom lane
On Fri, Sep 5, 2025 at 7:14 AM PALAYRET Jacques <jacques.palayret@meteo.fr> wrote:
Hello,
In a PL/pgSQL function, there is no command for sharing a common part of the body of several functions, is there?
In my case, I would like a function that returns a numeric value; this value is associated with several other parameters, some numeric and others textual.
It would not be practical for maintenance to have several functions (one function for each given parameter) with much of the code being identical.
Presently, I have a text function with all the parameters in return and a function for each parameter based on the first one (with all the parameters), using substr(), position(), etc.
For me, I think it would be easier (and perhaps faster) to use some kind of #include in the body of all the functions.
Can you provide some examples of what you are trying to accomplish?
There may be some workarounds using immutable functions other tricks, but I'm not 100% sure I have my head around your issue.
merlin
On Fri, Sep 5, 2025 at 9:14 AM PALAYRET Jacques <jacques.palayret@meteo.fr> wrote:
Hello,
In a PL/pgSQL function, there is no command for sharing a common part of the body of several functions, is there?
In my case, I would like a function that returns a numeric value; this value is associated with several other parameters, some numeric and others textual.
It would not be practical for maintenance to have several functions (one function for each given parameter) with much of the code being identical.
Presently, I have a text function with all the parameters in return and a function for each parameter based on the first one (with all the parameters), using substr(), position(), etc.
What about something like a "parent" function which a bunch of "stub" functions call? The stub functions would have different parameter types, know what to call the parent function with, and how to return the correct number value.
Because of name overloading, they might even have the same name.
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Hello,
Thanks for your response.
# Currently, I have a function text and a function array with the same body but a distinct type return.
-> Example with the array of text function :
SELECT public.calfxi3s_all_elements_text_array(12345678, '2025-01-01 00:00') ;
calfxi3s_all_elements_text_array
-------------------------------------------------------------------------
{3.2,sonic,"Capteur Vent ultrasonique compact Brand xxx",2,-0.123,0.321}
That gives some parameters : the value of the wind strength (3.2), the kind of sensor (sonic), the model (Capteur ...), the environmment (2), two coefficients (-0.123,0.321)
Then I have several functions using the previous one, for example public.calfxi3s_value() :
CREATE OR REPLACE FUNCTION public.calfxi3s_value(np integer, dt timestamp without time zone)
RETURNS numeric
LANGUAGE sql
STABLE
AS $function$
SELECT (public.calfxi3s_all_elements_text_array(np,dt))[1]::numeric ;
$function$
-> Example with the array of text function :
SELECT public.calfxi3s_all_elements_text_array(12345678, '2025-01-01 00:00') ;
calfxi3s_all_elements_text_array
-------------------------------------------------------------------------
{3.2,sonic,"Capteur Vent ultrasonique compact Brand xxx",2,-0.123,0.321}
That gives some parameters : the value of the wind strength (3.2), the kind of sensor (sonic), the model (Capteur ...), the environmment (2), two coefficients (-0.123,0.321)
Then I have several functions using the previous one, for example public.calfxi3s_value() :
CREATE OR REPLACE FUNCTION public.calfxi3s_value(np integer, dt timestamp without time zone)
RETURNS numeric
LANGUAGE sql
STABLE
AS $function$
SELECT (public.calfxi3s_all_elements_text_array(np,dt))[1]::numeric ;
$function$
I can manage with this method, but it shoud be better with a include directive.
Now, it doesn't exist. so I will do in an other way
A+
De: "Merlin Moncure" <mmoncure@gmail.com>
À: "PALAYRET Jacques" <jacques.palayret@meteo.fr>
Cc: pgsql-general@lists.postgresql.org
Envoyé: Vendredi 5 Septembre 2025 18:21:08
Objet: Re: PostgreSQL include directive in plpgsql language PL/pgSQL
À: "PALAYRET Jacques" <jacques.palayret@meteo.fr>
Cc: pgsql-general@lists.postgresql.org
Envoyé: Vendredi 5 Septembre 2025 18:21:08
Objet: Re: PostgreSQL include directive in plpgsql language PL/pgSQL
On Fri, Sep 5, 2025 at 7:14 AM PALAYRET Jacques <jacques.palayret@meteo.fr> wrote:
Hello,
In a PL/pgSQL function, there is no command for sharing a common part of the body of several functions, is there?
In my case, I would like a function that returns a numeric value; this value is associated with several other parameters, some numeric and others textual.
It would not be practical for maintenance to have several functions (one function for each given parameter) with much of the code being identical.
Presently, I have a text function with all the parameters in return and a function for each parameter based on the first one (with all the parameters), using substr(), position(), etc.
For me, I think it would be easier (and perhaps faster) to use some kind of #include in the body of all the functions.
Can you provide some examples of what you are trying to accomplish?
There may be some workarounds using immutable functions other tricks, but I'm not 100% sure I have my head around your issue.
merlin
On Mon, Sep 8, 2025 at 5:37 AM PALAYRET Jacques <jacques.palayret@meteo.fr> wrote:
I can manage with this method, but it shoud be better with a include directive.
Even if we had includes, I would strongly advise "this method" which seems pretty clean and maintainable. What is the issue with SQL functions that call a parent function and slightly manipulate the results?
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
On Mon, Sep 8, 2025 at 3:37 AM PALAYRET Jacques <jacques.palayret@meteo.fr> wrote:
Hello,Thanks for your response.# Currently, I have a function text and a function array with the same body but a distinct type return.
-> Example with the array of text function :
SELECT public.calfxi3s_all_elements_text_array(12345678, '2025-01-01 00:00') ;
calfxi3s_all_elements_text_array
-------------------------------------------------------------------------
{3.2,sonic,"Capteur Vent ultrasonique compact Brand xxx",2,-0.123,0.321}
That gives some parameters : the value of the wind strength (3.2), the kind of sensor (sonic), the model (Capteur ...), the environmment (2), two coefficients (-0.123,0.321)
Right. I guess your issue might be in calfxi3s_all_elements_text_array().
Is there any specific reason why this must return an array vs a composite type? I guess the basic issue here is trying to abuse the array type when a composite type might have many advantages here.
CREATE TYPE sensor_measurement_t AS
(
wind_strength numeric,
sensor text,
model text,
environment int,
coefficient1 numeric,
coefficient2 numeric
);
CREATE FUNCTION calfxi3s_all_elements(...) RETURNS sensor_measurement_t AS ..
SELECT * FROM calfxi3s_all_elements(...);
SELECT model FROM calfxi3s_all_elements(...);
melin