Re: Proposing WITH ITERATIVE

On Wed, Apr 29, 2020 at 10:34 AM Jonah H. Harris <jonah(dot)harris(at)gmail(dot)com>
wrote:

> On Wed, Apr 29, 2020 at 7:22 AM Peter Eisentraut <
> peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>
>> Yeah the RECURSIVE vs ITERATIVE is a bit of a red herring here. As you
>> say, the RECURSIVE keyword doesn't specify the processing but marks the
>> fact that the specification of the query is recursive.
>>
>
> Agreed. I started thinking through Fabien's response last night.
>
> I think a syntax that would fit better within the existing framework
>> would be something like
>>
>> WITH RECURSIVE t AS (
>> SELECT base case
>> REPLACE ALL -- instead of UNION ALL
>> SELECT recursive case
>> )
>>
>
> I was originally thinking more along the lines of Fabien's approach, but
> this is similarly interesting.
>

Obviously I'm very concerned about doing something that the SQL Standard
will clobber somewhere down the road. Having said that, the recursive
syntax always struck me as awkward even by SQL standards.

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 ,
so the only syntax here without precedence is dropping a WHILE into an
UPDATE statement.