MIME-Version: 1.0
References: 
 <CAGH1kmyB7D7+xj4=HkEPPNoK1irm4OdgOMZe-GDqFWsLAa9m-A@mail.gmail.com>
In-Reply-To: 
 <CAGH1kmyB7D7+xj4=HkEPPNoK1irm4OdgOMZe-GDqFWsLAa9m-A@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Wed, 18 Dec 2024 09:06:40 -0500
Message-ID: 
 <CAKAnmmL34MAd436EJxXx6vV-3QHQvQDr3TQb5AjbuyzEwm=GKw@mail.gmail.com>
Subject: Re: Abitity to identify the current iteration in a recursive SELECT
 (feature request)
To: sulfinu@gmail.com
Cc: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000077568506298bee2b"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKAnmmL34MAd436EJxXx6vV-3QHQvQDr3TQb5AjbuyzEwm%3DGKw%40mail.gmail.com>
Precedence: bulk

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

On Wed, Dec 18, 2024 at 5:00=E2=80=AFAM <sulfinu@gmail.com> wrote:

> Is there a way to obtain directly this iteration sequence number within
> the SELECT statement following the UNION keyword in a recursive
> construction? I know it can by obtained by maintaining its value in a
> working table column, but that's suboptimal - I need it as a "magic"
> variable, akin, for example, the excluded variable available inside the O=
N
> CONFLICT DO UPDATE clause of an INSERT statement.
>

Do you mean something like "... WHERE pg_magic_iteration_number < 10"?
Looking at the source code, I don't see a trivial way to accomplish that.
Maintaining the count as a column in your select is still the canonical
way. As someone who writes a lot of recursive CTEs (especially each
December!), I'm not sure how useful this feature would be, as the number of
loops is rarely the criteria for ending the iterations.

I'm using the a recursive SELECT in order to join iteratively several
> (virtual) tables computed dinamically based on the iteration number.
>

Certainly the best solution is to use pl/pgsql, which gets you iterative
loops, lots of introspection and ways to break out of the loop, and even
true recursion.

Cheers,
Greg

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

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Dec 18, 2024 at 5:00=E2=80=AFAM &=
lt;<a href=3D"mailto:sulfinu@gmail.com">sulfinu@gmail.com</a>&gt; wrote:</d=
iv><div class=3D"gmail_quote gmail_quote_container"><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex"><div dir=3D"ltr"><div>Is there a way to obtain d=
irectly this iteration sequence number within the <span style=3D"font-famil=
y:monospace">SELECT</span> statement following the <span style=3D"font-fami=
ly:monospace">UNION</span> keyword in a recursive construction? I know it c=
an by obtained by maintaining its value in a working table column, but that=
&#39;s suboptimal - I need it as a &quot;magic&quot; variable, akin, for ex=
ample, the <span style=3D"font-family:monospace">excluded</span> variable a=
vailable inside the <span style=3D"font-family:monospace">ON CONFLICT DO UP=
DATE</span> clause of an <span style=3D"font-family:monospace">INSERT</span=
> statement.</div></div></blockquote><div><br></div><div>Do you mean someth=
ing like &quot;... WHERE pg_magic_iteration_number &lt; 10&quot;? Looking a=
t the source code, I don&#39;t see a trivial way to accomplish that. Mainta=
ining=C2=A0the count as a column in your select is still the canonical way.=
 As someone who writes a lot of recursive CTEs (especially each December!),=
 I&#39;m not sure how useful this feature would be, as the number of loops =
is rarely the criteria for ending the iterations.</div><div><br></div><bloc=
kquote 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"><div>I&#39;m =
using the a recursive <span style=3D"font-family:monospace">SELECT</span> i=
n order to join iteratively several (virtual) tables computed dinamically b=
ased on the iteration number. </div></div></blockquote><div><br></div><div>=
Certainly the best solution is to use pl/pgsql, which gets you iterative lo=
ops, lots of introspection=C2=A0and ways to break out of the loop, and even=
 true recursion.</div><div><br></div><div>Cheers,</div><div>Greg</div><div>=
<br></div></div></div>

--00000000000077568506298bee2b--