Re: Proposing WITH ITERATIVE

>
>
> > Perhaps something like this would be more readable
> >
> > WITH t AS (
> > UPDATE ( SELECT 1 AS ctr, 'x' as val )
> > SET ctr = ctr + 1, val = val || 'x'
> > WHILE ctr <= 100
> > RETURNING ctr, val
> > )
> >
> > The notion of an UPDATE on an ephemeral subquery isn't that special, see
> > "subquery2" in
> >
> https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/update_statement.htm
> ,
>
> I must admit that I do not like much needing another level of subquery,
> but maybe it could just be another named query in the WITH statement.
>

So like this:
WITH initial_conditions as (SELECT 1 as ctr, 'x' as val)
UPDATE initial_conditions
SET ctr = ctr + 1, val = val || 'x'
WHILE ctr <= 100
RETURNING ctr, val

> ISTM that UPDATE is quite restrictive as the number of rows cannot
> change, which does not seem desirable at all? How could I add or remove
> rows from one iteration to the next?
>

My understanding was that maintaining a fixed number of rows was a desired
feature.

> ISTM that the WHILE would be checked before updating, so that WHILE FALSE
> does nothing, in which case its position after SET is odd.
>

True, but having the SELECT before the FROM is equally odd.

> Having both WHERE and WHILE might look awkward.
>

Maybe an UNTIL instead of WHILE?

>
> Also it looks much more procedural this way, which is the point, but also
> depart from the declarative SELECT approach of WITH RECURSIVE.
>

Yeah, just throwing it out as a possibility. Looking again at what I
suggested, it looks a bit like the Oracle "CONNECT BY level <= x" idiom.

I suspect that the SQL standards body already has some preliminary work
done, and we should ultimately follow that.