Hi,
the algorithm present
here 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.
