MIME-Version: 1.0
References: <CANzqJaBWNharGuTCY1OCzgU7S5zOCdKS84fCW=X8=AiVy1ibyg@mail.gmail.com>
In-Reply-To: <CANzqJaBWNharGuTCY1OCzgU7S5zOCdKS84fCW=X8=AiVy1ibyg@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Tue, 19 Nov 2024 08:55:39 -0700
Message-ID: <CAKFQuwbFBn6uV1qczBXOZjd0r=zeH9s1KN4CQD_Nj=Lg17_GLQ@mail.gmail.com>
Subject: Re: Specifying columns returned by a function, when the function is
 in a SELECT column list?
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000bb1f9106274612c3"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwbFBn6uV1qczBXOZjd0r%3DzeH9s1KN4CQD_Nj%3DLg17_GLQ%40mail.gmail.com>
Precedence: bulk

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

On Tue, Nov 19, 2024 at 8:48=E2=80=AFAM Ron Johnson <ronljohnsonjr@gmail.co=
m> wrote:

> It's trivial to specify columns when a table-returning function is the
> FROM clause, but how does one specify columns when the table-returning
> function is a column in a SELECT clause?
>

You don't/cannot.  And with lateral you shouldn't find the need to hack
around it either.  Non-scalar function calls in the select clause are now
obsolete.

In the select clause the function call returns a single-column of composite
type with the names of the fields in the composite already known.  As the
query scope where you invoke the function you should only use the
composite.  If you need to dive into its fields you'd need a subquery -
ideally one that doesn't risk being optimized away due to a
multiple-evaluation hazard.

David J.

--000000000000bb1f9106274612c3
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, Nov 19, 2024 at 8:48=E2=80=AFAM Ron Johnson &lt;<a=
 href=3D"mailto:ronljohnsonjr@gmail.com">ronljohnsonjr@gmail.com</a>&gt; wr=
ote:</span></div></div><div class=3D"gmail_quote"><blockquote class=3D"gmai=
l_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,20=
4,204);padding-left:1ex"><div dir=3D"ltr"><div>It&#39;s trivial to specify =
columns when a table-returning function is the FROM clause, but how does on=
e specify columns when the table-returning function is a column in a SELECT=
 clause?</div></div></blockquote><div><br></div><div><div class=3D"gmail_de=
fault" style=3D"font-family:arial,helvetica,sans-serif">You don&#39;t/canno=
t.=C2=A0 And with lateral you shouldn&#39;t find the need to hack around=C2=
=A0it either.=C2=A0 Non-scalar function=C2=A0calls in the select clause are=
 now obsolete.</div></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:arial,helvetica,sans-serif">In the select clause the functi=
on call returns a single-column of composite type with the names of the fie=
lds in the composite already known.=C2=A0 As the query scope where you invo=
ke the function you should only use the composite.=C2=A0 If you need to div=
e into its fields you&#39;d need a subquery - ideally one that doesn&#39;t =
risk being optimized away due to a multiple-evaluation hazard.</div><div cl=
ass=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif"><br>=
</div><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,san=
s-serif">David J.</div></div></div>

--000000000000bb1f9106274612c3--