MIME-Version: 1.0
References: <CACX+KaPBaSW+0PyMC7dmY6W9W8mPSbWpkJRhAOQOC9Qb0TjCQA@mail.gmail.com>
In-Reply-To: <CACX+KaPBaSW+0PyMC7dmY6W9W8mPSbWpkJRhAOQOC9Qb0TjCQA@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Tue, 18 Jun 2024 10:13:08 -0700
Message-ID: <CAKFQuwbtbhkbw439S3_UbraTpyHfQGxdjjhBBUbfKPd4PVC+xg@mail.gmail.com>
Subject: Re: Seeking Clarification on Function Definitions in PostgreSQL Extensions
To: Ayush Vatsa <ayushvatsa1810@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000005c5702061b2d34ad"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwbtbhkbw439S3_UbraTpyHfQGxdjjhBBUbfKPd4PVC%2Bxg%40mail.gmail.com>
Precedence: bulk

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

On Tue, Jun 18, 2024 at 9:50=E2=80=AFAM Ayush Vatsa <ayushvatsa1810@gmail.c=
om>
wrote:

> 1/ I wanted to know what's the difference between the above three
> definitions.
> As per my understanding, "fun1" and "fun2" look the same, taking one
> integer and returning two columns with multiple rows.
>

Yes.


>
> Can the above definition (used for fun1 and fun2) cover both single and
> multiple row scenarios.
>

In so far as one is a valid number of rows to return from a function that
returns zero or more rows, yes.  But if the function is incapable of
returning more than one result it should not be marked with table/setof on
semantic grounds.


> 2/ How does someone decide which type of definition is to be used?
>
>
Between 1 and 2 is a style choice.  I prefer TABLE.  Using setof is more
useful when the returned type is predefined.  Or a true record where the
caller has to specify the shape.

For 3, having a non-set-returning-function that outputs multiple columns is
just odd, IMO.  Personally I'd go for pre-defining a composite type, then
return that type.

David J.

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

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif"><span style=3D"font-family:Arial,Helve=
tica,sans-serif">On Tue, Jun 18, 2024 at 9:50=E2=80=AFAM Ayush Vatsa &lt;<a=
 href=3D"mailto:ayushvatsa1810@gmail.com">ayushvatsa1810@gmail.com</a>&gt; =
wrote:</span><br></div></div><div class=3D"gmail_quote"><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>1/ I wanted to know =
what&#39;s the difference between the above three definitions. <br>As per m=
y understanding, &quot;fun1&quot; and &quot;fun2&quot; look the same, takin=
g one integer and returning two columns with multiple rows.</div></div></bl=
ockquote><div><br></div><div class=3D"gmail_default" style=3D"font-family:a=
rial,helvetica,sans-serif">Yes.</div><div>=C2=A0</div><blockquote class=3D"=
gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(20=
4,204,204);padding-left:1ex"><div dir=3D"ltr"><div> <br>Can the above defin=
ition=C2=A0(used for fun1 and fun2) cover both single and multiple row scen=
arios.<br></div></div></blockquote><div><br></div><div><div class=3D"gmail_=
default" style=3D"font-family:arial,helvetica,sans-serif">In so far as one =
is a valid number of rows to return from a function that returns zero or mo=
re rows, yes.=C2=A0 But if the function is incapable of returning more than=
 one result it should not be marked with table/setof on semantic grounds.</=
div></div><div><br></div><blockquote class=3D"gmail_quote" style=3D"margin:=
0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">=
<div dir=3D"ltr"><br>2/ How does someone decide which type of definition is=
 to be used?<br><br></div></blockquote><div><br></div><div class=3D"gmail_d=
efault" style=3D"font-family:arial,helvetica,sans-serif">Between 1 and 2 is=
 a style choice.=C2=A0 I prefer TABLE.=C2=A0 Using setof is more useful whe=
n the returned type=C2=A0is predefined.=C2=A0 Or a true record where the ca=
ller has to specify the shape.</div><div class=3D"gmail_default" style=3D"f=
ont-family:arial,helvetica,sans-serif"><br></div><div class=3D"gmail_defaul=
t" style=3D"font-family:arial,helvetica,sans-serif">For 3, having a non-set=
-returning-function that outputs multiple columns is just odd, IMO.=C2=A0 P=
ersonally I&#39;d go for pre-defining a composite type, then return that ty=
pe.</div><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,=
sans-serif"><br></div><div class=3D"gmail_default" style=3D"font-family:ari=
al,helvetica,sans-serif">David J.</div><div class=3D"gmail_default" style=
=3D"font-family:arial,helvetica,sans-serif"><br></div></div></div>

--0000000000005c5702061b2d34ad--