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.