MIME-Version: 1.0
References: <1985501673.206991527.1757078030654.JavaMail.zimbra@meteo.fr>
 <CAHyXU0zNE3CuPs2433GZtVh9SbrkyTUwGmAiy+yJuhz484gPUw@mail.gmail.com> <890069544.207914575.1757324233380.JavaMail.zimbra@meteo.fr>
In-Reply-To: <890069544.207914575.1757324233380.JavaMail.zimbra@meteo.fr>
From: Merlin Moncure <mmoncure@gmail.com>
Date: Mon, 8 Sep 2025 10:31:52 -0600
Message-ID: <CAHyXU0wz3i=xgSKzMfoC_57Ya3m2FO=Y=rzmLVcWEJF=hWv4cw@mail.gmail.com>
Subject: Re: PostgreSQL include directive in plpgsql language PL/pgSQL
To: PALAYRET Jacques <jacques.palayret@meteo.fr>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000569086063e4cba0f"
Archived-At: <https://www.postgresql.org/message-id/CAHyXU0wz3i%3DxgSKzMfoC_57Ya3m2FO%3DY%3DrzmLVcWEJF%3DhWv4cw%40mail.gmail.com>
Precedence: bulk

--000000000000569086063e4cba0f
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Sep 8, 2025 at 3:37=E2=80=AFAM PALAYRET Jacques <jacques.palayret@m=
eteo.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), tw=
o
> 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

--000000000000569086063e4cba0f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Sep 8, =
2025 at 3:37=E2=80=AFAM PALAYRET Jacques &lt;<a href=3D"mailto:jacques.pala=
yret@meteo.fr">jacques.palayret@meteo.fr</a>&gt; wrote:<br></div><blockquot=
e class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px s=
olid rgb(204,204,204);padding-left:1ex"><div><div style=3D"font-family:gara=
mond,&quot;new york&quot;,times,serif;font-size:12pt;color:rgb(0,0,0)"><div=
> <div>Hello,</div><div>Thanks for your response.</div><div><br></div><div>=
# Currently, I have a function text and a function array with <strong>the s=
ame body</strong> but a <strong>distinct type return</strong>.<br>-&gt; Exa=
mple with the array of text function :<br>SELECT public.calfxi3s_all_elemen=
ts_text_array(12345678, &#39;2025-01-01 00:00&#39;) ;<br>=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0 calfxi3s_all_elements_text_array<br>-----------------=
--------------------------------------------------------<br>=C2=A0{3.2,soni=
c,&quot;Capteur Vent ultrasonique compact Brand xxx&quot;,2,-0.123,0.321}<b=
r>That gives some parameters : the value of the wind strength (3.2), the ki=
nd of sensor (sonic), the model (Capteur ...), the environmment (2), two co=
efficients (-0.123,0.321)</div></div></div></div></blockquote><div><br></di=
v><div>Right.=C2=A0 I guess your issue might be in=C2=A0calfxi3s_all_elemen=
ts_text_array().</div><div><br></div><div>Is there any specific reason why =
this must return an array vs a composite type?=C2=A0 I guess the basic issu=
e here is trying to abuse the array type when a composite type might have m=
any advantages here.</div><div><br></div><div>CREATE TYPE sensor_measuremen=
t_t AS</div><div>(</div><div>=C2=A0 wind_strength numeric,</div><div>=C2=A0=
 sensor text,</div><div>=C2=A0 model text,</div><div>=C2=A0 environment int=
,</div><div>=C2=A0 coefficient1 numeric,</div><div>=C2=A0 coefficient2 nume=
ric</div><div>);</div><div><br></div><div>CREATE FUNCTION calfxi3s_all_elem=
ents(...) RETURNS sensor_measurement_t=C2=A0 AS ..</div><div><br></div><div=
><br></div><div>SELECT * FROM calfxi3s_all_elements(...);</div><div>SELECT =
model FROM calfxi3s_all_elements(...);</div><div><br></div><div>melin</div>=
<div>=C2=A0</div></div></div>

--000000000000569086063e4cba0f--