Re: Proposing WITH ITERATIVE

Hello Jonah,

Nice.

> -- No ORDER/LIMIT is required with ITERATIVE as only a single tuple is
> present
> EXPLAIN ANALYZE
> WITH ITERATIVE fib_sum (iteration, previous_number, new_number)
>  AS (SELECT 1, 0::numeric, 1::numeric
>       UNION ALL
>      SELECT (iteration + 1), new_number, (previous_number + new_number)
>        FROM fib_sum
>       WHERE iteration <= 10000)
> SELECT r.iteration, r.new_number
>  FROM fib_sum r;

Nice.

My 0,02€ about the feature design:

I'm wondering about how to use such a feature in the context of WITH query 
with several queries having different behaviors. Currently "WITH" 
introduces a named-query (like a view), "WITH RECURSIVE" introduces a mix 
of recursive and named queries, pg really sees whether each one is 
recursive or not, and "RECURSIVE" is required but could just be guessed.

Now that there could be 3 variants in the mix, and for the feature to be 
orthogonal I think that it should be allowed. However, there is no obvious 
way to distinguish a RECURSIVE from an ITERATIVE, as it is more a 
behavioral thing than a structural one. This suggests allowing to tag each 
query somehow, eg before, which would be closer to the current approach:

   WITH
     foo(i) AS (simple select),
     RECURSIVE bla(i) AS (recursive select),
     ITERATIVE blup(i) AS (iterative select),

or maybe after AS, which may be clearer because closer to the actual 
query, which looks better to me:

   WITH
     foo(i) AS (simple select),
     bla(i) AS RECURSIVE (recursive select),
     boo(i) AS ITERATIVE (iterative select),
     …


Also, with 3 cases I would prefer that the default has a name so someone 
can talk about it otherwise than saying "default". Maybe SIMPLE would 
suffice, or something else. ISTM that as nothing is expected between AS 
and the open parenthesis, there is no need to have a reserved keyword, 
which is a good thing for the parser.

-- 
Fabien.