MIME-Version: 1.0
From: sulfinu@gmail.com
Date: Mon, 16 Dec 2024 12:21:03 +0200
Message-ID: 
 <CAGH1kmyB7D7+xj4=HkEPPNoK1irm4OdgOMZe-GDqFWsLAa9m-A@mail.gmail.com>
Subject: Abitity to identify the current iteration in a recursive SELECT
 (feature request)
To: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000004d1e940629608a93"
Archived-At: 
 <https://www.postgresql.org/message-id/CAGH1kmyB7D7%2Bxj4%3DHkEPPNoK1irm4OdgOMZe-GDqFWsLAa9m-A%40mail.gmail.com>
Precedence: bulk

--0000000000004d1e940629608a93
Content-Type: text/plain; charset="UTF-8"

Hi,

the algorithm present here
<https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE>
for the resolution of WITH RECURSIVE queries is, broadly speaking,
iterative, with the iteration sequence number indicated in the first row
item of the field generated by the SEARCH BREADTH FIRST clause, i.e. *0*,
*1*, *2* etc.

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 ON CONFLICT DO UPDATE clause of
an INSERT statement.

*Background*
I'm using the a recursive SELECT in order to join iteratively several
(virtual) tables computed dinamically based on the iteration number. Why?
In order to implement a set intersection, with each set extracted from a
jsonb column of a table.
If the iteration number was a distinct column of the working table, the
table joined in each iteration would need use LATERAL, thus building it for
as many times as the number of rows in the working table instead of *just
once*.

*Workaround*
I simulated the said missing variable with a sequence, which is cumbersome
because;

   - the sequence is single-use, so it must be named randomly, created
   before and dropped after the WITH RECURSIVE ... SELECT statement;
   - nextval('sequence_name') *cannot* be used directly within WHERE
   clauses, because it is re-evaluated for each row; the next sequence value
   must be reached through a phrase like join (select
   nextval('sequence_name')) in order to ensure a single evaluation per
   iteration.

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

<div dir=3D"ltr"><div>Hi,</div><div><br></div><div>the algorithm present <a=
 href=3D"https://www.postgresql.org/docs/current/queries-with.html#QUERIES-=
WITH-RECURSIVE">here</a> for the resolution of <span style=3D"font-family:m=
onospace">WITH RECURSIVE</span> queries is, broadly speaking, iterative, wi=
th the iteration sequence number indicated in the first row item of the fie=
ld generated by the <span style=3D"font-family:monospace">SEARCH BREADTH FI=
RST</span> clause, i.e. <i>0</i>, <i>1</i>, <i>2</i> etc.</div><div><br></d=
iv><div>Is there a way to obtain directly this iteration sequence number wi=
thin the <span style=3D"font-family:monospace">SELECT</span> statement foll=
owing the <span style=3D"font-family:monospace">UNION</span> keyword in a r=
ecursive construction? I know it can by obtained by maintaining its value i=
n a working table column, but that&#39;s suboptimal - I need it as a &quot;=
magic&quot; variable, akin, for example, the <span style=3D"font-family:mon=
ospace">excluded</span> variable available inside the <span style=3D"font-f=
amily:monospace">ON CONFLICT DO UPDATE</span> clause of an <span style=3D"f=
ont-family:monospace">INSERT</span> statement.</div><div><br></div><div><b>=
Background</b></div><div>I&#39;m using the a recursive <span style=3D"font-=
family:monospace">SELECT</span> in order to join iteratively several (virtu=
al) tables computed dinamically based on the iteration number. Why? In orde=
r to implement a set intersection, with each set extracted from a <span sty=
le=3D"font-family:monospace">jsonb</span> column of a table.</div><div>If t=
he iteration number was a distinct column of the working table, the table j=
oined in each iteration would need use <span style=3D"font-family:monospace=
">LATERAL</span>, thus building it for as many times as the number of rows =
in the working table instead of <i>just once</i>.<br></div><div><br></div><=
div><b>Workaround</b></div><div>I simulated the said missing variable with =
a sequence, which is cumbersome because;</div><div><ul><li>the sequence is =
single-use, so it must be named randomly, created before and dropped after =
the <span style=3D"font-family:monospace">WITH RECURSIVE ... SELECT</span> =
statement;</li><li><span style=3D"font-family:monospace">nextval(&#39;seque=
nce_name&#39;)</span> <b>cannot</b> be used directly within <span style=3D"=
font-family:monospace">WHERE</span> clauses, because it is re-evaluated for=
 each row; the next sequence value must be reached through a phrase like <s=
pan style=3D"font-family:monospace">join (select nextval(&#39;sequence_name=
&#39;))</span> in order to ensure a single evaluation per iteration.<br></l=
i></ul></div></div>

--0000000000004d1e940629608a93--