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

On Wed, Dec 18, 2024 at 5:00 AM <sulfinu(at)gmail(dot)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 ON
> 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