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.
