Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uvYJO-00FJqE-FP for pgsql-general@arkaria.postgresql.org; Mon, 08 Sep 2025 09:37:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uvYJM-003PNW-FK for pgsql-general@arkaria.postgresql.org; Mon, 08 Sep 2025 09:37:20 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uvYJM-003PNO-0K for pgsql-general@lists.postgresql.org; Mon, 08 Sep 2025 09:37:20 +0000 Received: from zcsmtaf02-pub.meteo.fr ([137.129.63.6]) by makus.postgresql.org with smtp (Exim 4.96) (envelope-from ) id 1uvYJJ-0018Hg-0p for pgsql-general@lists.postgresql.org; Mon, 08 Sep 2025 09:37:19 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by zcsmtaf02-pub.meteo.fr (Postfix) with ESMTP id D3FF8431E364; Mon, 8 Sep 2025 09:37:13 +0000 (GMT) Received: from zcsmtaf02-pub.meteo.fr ([127.0.0.1]) by localhost (zcsmtaf02.meteo.fr [127.0.0.1]) (amavis, port 10032) with ESMTP id Tnn_OB6U2TsP; Mon, 8 Sep 2025 09:37:13 +0000 (GMT) Received: from localhost (localhost.localdomain [127.0.0.1]) by zcsmtaf02-pub.meteo.fr (Postfix) with ESMTP id BAEE0431E363; Mon, 8 Sep 2025 09:37:13 +0000 (GMT) X-Virus-Scanned: amavis at meteo.fr Received: from zcsmtaf02-pub.meteo.fr ([127.0.0.1]) by localhost (zcsmtaf02.meteo.fr [127.0.0.1]) (amavis, port 10026) with ESMTP id wylcrZhnPUit; Mon, 8 Sep 2025 09:37:13 +0000 (GMT) Received: from zcsmsm04.meteo.fr (zcsmsm04.meteo.fr [172.24.3.124]) by zcsmtaf02-pub.meteo.fr (Postfix) with ESMTP id A650E4271196; Mon, 8 Sep 2025 09:37:13 +0000 (GMT) Date: Mon, 8 Sep 2025 09:37:13 +0000 (GMT) From: PALAYRET Jacques To: Merlin Moncure Cc: pgsql-general@lists.postgresql.org Message-ID: <890069544.207914575.1757324233380.JavaMail.zimbra@meteo.fr> In-Reply-To: References: <1985501673.206991527.1757078030654.JavaMail.zimbra@meteo.fr> Subject: Re: PostgreSQL include directive in plpgsql language PL/pgSQL MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="=_6d808bb4-d3c2-464b-8163-89fa3fc6c813" X-Originating-IP: [172.24.2.156] X-Mailer: Zimbra 9.0.0_GA_4583 (ZimbraWebClient - FF128 (Win)/9.0.0_GA_4583) Thread-Topic: PostgreSQL include directive in plpgsql language PL/pgSQL Thread-Index: h4PZkBhBHKfIgxYXRJHtPcmV/lugrA== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_6d808bb4-d3c2-464b-8163-89fa3fc6c813 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hello,=20 Thanks for your response.=20 # Currently, I have a function text and a function array with the same body= but a distinct type return .=20 -> Example with the array of text function :=20 SELECT public.calfxi3s_all_elements_text_array(12345678, '2025-01-01 00:00'= ) ;=20 calfxi3s_all_elements_text_array=20 -------------------------------------------------------------------------= =20 {3.2,sonic,"Capteur Vent ultrasonique compact Brand xxx",2,-0.123,0.321}=20 That gives some parameters : the value of the wind strength (3.2), the kind= of sensor (sonic), the model (Capteur ...), the environmment (2), two coef= ficients (-0.123,0.321)=20 Then I have several functions using the previous one, for example public.ca= lfxi3s_value() :=20 CREATE OR REPLACE FUNCTION public.calfxi3s_value(np integer, dt timestamp w= ithout time zone)=20 RETURNS numeric=20 LANGUAGE sql=20 STABLE=20 AS $function$=20 SELECT (public.calfxi3s_all_elements_text_array(np,dt))[1]::numeric ;=20 $function$=20 I can manage with this method, but it shoud be better with a include direct= ive.=20 Now, it doesn't exist. so I will do in an other way=20 A+=20 De: "Merlin Moncure" =20 =C3=80: "PALAYRET Jacques" =20 Cc: pgsql-general@lists.postgresql.org=20 Envoy=C3=A9: Vendredi 5 Septembre 2025 18:21:08=20 Objet: Re: PostgreSQL include directive in plpgsql language PL/pgSQL=20 On Fri, Sep 5, 2025 at 7:14 AM PALAYRET Jacques < [ mailto:jacques.palayret= @meteo.fr | jacques.palayret@meteo.fr ] > wrote:=20 Hello,=20 In a PL/pgSQL function, there is no command for sharing a common part of th= e body of several functions, is there?=20 In my case, I would like a function that returns a numeric value; this valu= e is associated with several other parameters, some numeric and others text= ual.=20 It would not be practical for maintenance to have several functions (one fu= nction for each given parameter) with much of the code being identical.=20 Presently, I have a text function with all the parameters in return and a f= unction for each parameter based on the first one (with all the parameters)= , using substr(), position(), etc.=20 For me, I think it would be easier (and perhaps faster) to use some kind of= #include in the body of all the functions.=20 Can you provide some examples of what you are trying to accomplish?=20 There may be some workarounds using immutable functions other tricks, but I= 'm not 100% sure I have my head around your issue.=20 merlin=20 --=_6d808bb4-d3c2-464b-8163-89fa3fc6c813 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Hello,
<= div>Thanks for your response.

# Currently, I have a function text and a function array with the = same body but a distinct type return.
-> Ex= ample with the array of text function :
SELECT public.calfxi3s_all_eleme= nts_text_array(12345678, '2025-01-01 00:00') ;
    &= nbsp;           &nbs= p;   calfxi3s_all_elements_text_array
------------------------= -------------------------------------------------
 {3.2,sonic,"Capt= eur 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 (son= ic), the model (Capteur ...), the environmment (2), two coefficients (-0.12= 3,0.321)

Then I have several functions using the previous one, for e= xample public.calfxi3s_value() :

CREATE OR REPLACE FUNCTION public.c= alfxi3s_value(np integer, dt timestamp without time zone)
 RETURNS = numeric
 LANGUAGE sql
 STABLE
AS $function$
SELECT (p= ublic.calfxi3s_all_elements_text_array(np,dt))[1]::numeric ;
$function$<= br>

I can manage with this method, but it shoud be= better with a include directive.
Now, i= t doesn't exist. so I will do in an other way

A+
=
De: "Merlin Moncure" <mmoncure@gmail.com>
=C3=80: "PALAYRET Jacques" <jacques.palayret@meteo.fr>
Cc: pgsql-g= eneral@lists.postgresql.org
Envoy=C3=A9: Vendredi 5 Septembre 202= 5 18:21:08
Objet: Re: PostgreSQL include directive in plpgsql lan= guage PL/pgSQL

On Fri, Sep 5, 2025 at 7:14=E2=80=AFAM P= ALAYRET Jacques <jacques.palayret@meteo.fr&= gt; wrote:
Hello,
<= br>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 sever= al other parameters, some numeric and others textual.
It would not be pr= actical for maintenance to have several functions (one function for each gi= ven parameter) with much of the code being identical.

Presently, I h= ave a text function with all the parameters in return and a function for ea= ch parameter based on the first one (with all the parameters), using substr= (), position(), etc.
For me, I think it would be easier (and perhaps fas= ter) 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 workaro= unds using immutable functions other tricks, but I'm not 100% sure I have m= y head around your issue.

merlin

<= /div> --=_6d808bb4-d3c2-464b-8163-89fa3fc6c813--