Re: Abitity to identify the current iteration in a recursive SELECT (feature request)

Am 16.12.24 um 11:21 schrieb sulfinu@gmail.com:
> 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.
>

I assume you don't want to do something like the following when you
refer to "working table column"?

with recursive cte as (
    select ..., 1 as level
    from ...
    union all
    select ...., parent.level + 1
    from ...
      join cte as parent on ...
)