MIME-Version: 1.0
References: 
 <CAGH1kmyB7D7+xj4=HkEPPNoK1irm4OdgOMZe-GDqFWsLAa9m-A@mail.gmail.com>
 <CAKAnmmL34MAd436EJxXx6vV-3QHQvQDr3TQb5AjbuyzEwm=GKw@mail.gmail.com>
In-Reply-To: 
 <CAKAnmmL34MAd436EJxXx6vV-3QHQvQDr3TQb5AjbuyzEwm=GKw@mail.gmail.com>
From: sulfinu@gmail.com
Date: Thu, 19 Dec 2024 11:16:47 +0200
Message-ID: 
 <CAGH1kmxExkBY-tW8edPoN7XwwJuCgBpJs+5Fs5nEN5Fd1yFv4A@mail.gmail.com>
Subject: Re: Abitity to identify the current iteration in a recursive SELECT
 (feature request)
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000fe8cd306299bfd5d"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGH1kmxExkBY-tW8edPoN7XwwJuCgBpJs%2B5Fs5nEN5Fd1yFv4A%40mail.gmail.com>
Precedence: bulk

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

Please read among your lines.

=C3=8En mie., 18 dec. 2024 la 16:07, Greg Sabino Mullane <htamfids@gmail.co=
m> a
scris:

> 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.
>

Oh, I disagree, just told you that the iteration number is readily
available in the field computed by the SEARCH BREADTH FIRST clause.


> 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 never said I use the iteration number to end the process, I need it to
pick the right table to be joined. If the iteration number was stored
in a *working
table* column, I would be forced to perform a LATERAL join, which
recomputes the *same joined table* again and again for every row in the
working table.


>
> 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.
>

Thought about it, of course, but I'm pretty sure that plain JOINs are
quicker than linear search loops written in pl/pgsql (remember I need to
intersect an dynamic number of arrays) .

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

<div dir=3D"ltr"><div>Please read among your lines.<br></div><br><div class=
=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr=
">=C3=8En mie., 18 dec. 2024 la 16:07, Greg Sabino Mullane &lt;<a href=3D"m=
ailto:htamfids@gmail.com">htamfids@gmail.com</a>&gt; a scris:<br></div><blo=
ckquote 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 class=
=3D"gmail_quote"><div>Do you mean something like &quot;... WHERE pg_magic_i=
teration_number &lt; 10&quot;? Looking at the source code, I don&#39;t see =
a trivial way to accomplish that.</div></div></div></blockquote><div><br></=
div><div>Oh, I disagree, just told you that the iteration number is readily=
 available in the field computed by the <span style=3D"font-family:monospac=
e">SEARCH BREADTH FIRST</span> clause.<br></div><div>=C2=A0</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 dir=3D"ltr"><div class=3D"gmai=
l_quote"><div> Maintaining=C2=A0the count as a column in your select is sti=
ll the canonical way. As someone who writes a lot of recursive CTEs (especi=
ally 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></div></blockquote><div>=C2=A0</div><div>I never said I use the iter=
ation number to end the process, I need it to pick the right table to be jo=
ined. If the iteration number was stored in a <i>working table</i> column, =
I would be forced to perform a <span style=3D"font-family:monospace">LATERA=
L</span> join, which recomputes the <b>same joined table</b> again and agai=
n for every row in the working table.<br></div><div>=C2=A0</div><blockquote=
 class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px so=
lid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail=
_quote"><br><div>Certainly the best solution is to use pl/pgsql, which gets=
 you iterative loops, lots of introspection=C2=A0and ways to break out of t=
he loop, and even true recursion.</div></div></div></blockquote><div><br></=
div>Thought about it, of course, but I&#39;m pretty sure that plain JOINs a=
re quicker than linear search loops written in pl/pgsql (remember I need to=
 intersect an dynamic number of arrays) .<br></div></div>

--000000000000fe8cd306299bfd5d--