Re: Proposing WITH ITERATIVE - Mailing list pgsql-hackers

Hello,

more random thoughts about syntax, semantics, and keeping it relational.

> While I'm not a huge fan of it, one of the other databases implementing
> this functionality does so using the syntax:
>
> WITH ITERATIVE R AS '(' R0 ITERATE Ri UNTIL N (ITERATIONS | UPDATES) ')' Qf
>
> Where N in ITERATIONS represents termination at an explicit count and, in
> UPDATES, represents termination after Ri updates more than n rows on table
> R.
>
> One of the main reasons I dislike the above is that it assumes N is 
> known. In some cases, however, you really need termination upon a 
> condition.

Yes, definitely, a (boolean?) condition is really needed, but possibly 
above N could be an expression, maybe with some separator before the 
query.

ISTM that using SELECT iterations is relational and close to the currently 
existing RECURSIVE. Separating the initialization and iterations with 
ITERATE is kind of the same approach than Peter's REPLACE, somehow, i.e. a 
new marker.

The above approach bothers me because it changes the query syntax a lot. 
The inside-WITH syntax should be the same as the normal query syntax.

First try. If we go to new markers, maybe the following, which kind of 
reuse Corey explicit condition, but replacing UPDATE with SELECT which 
makes it more generic:

  WITH R AS (
    ITERATE [STARTING] FROM R0
    WHILE/UNTIL condition REPEAT Ri
  );

Ok, it is quite procedural. It is really just a reordering of the syntax 
shown above, with a boolean condition thrown in and a heavy on (key)words 
SQL-like look and feel. It seems to make sense on a simple example:

  -- 1 by 1 count
  WITH counter(n) (
    ITERATE STARTING FROM
      SELECT 1
    WHILE n < 10 REPEAT
      SELECT n+1 FROM counter
  );

However I'm very unclear about the WHILE stuff, it makes some sense here 
because there is just one row, but what if there are severals?

  -- 2 by 2 count
  WITH counter(n) (
    ITERATE [STARTING FROM? OVER? nothing?]
      SELECT 1 UNION SELECT 2 -- cannot be empty? why not?
    WHILE n < 10 REPEAT
      -- which n it is just above?
      -- shoult it add a ANY/ALL semantics?
      -- should it really be a sub-query returning a boolean?
      -- eg: WHILE TRUE = ANY/ALL (SELECT n < 10 FROM counter)
      -- which I find pretty ugly.
      -- what else could it be?
      SELECT n+2 FROM counter
  );

Also, the overall syntax does not make much sense outside a WITH because 
one cannot reference the initial query which has no name.

Hmmm. Not very convincing:-) Let us try again.

Basically iterating is a 3 select construct: one for initializing, one for 
iterating, one for the stopping condition, with naming issues, the last 
point being exactly what WITH should solve.

by restricting the syntax to normal existing selects and moving things 
out:

   WITH stuff(n) AS
     ITERATE OVER/FROM/STARTING FROM '(' initial-sub-query ')' -- or a table?
     WHILE/UNTIL '(' condition-sub-query ')'
     -- what is TRUE/FALSE? non empty? other?
     -- WHILE/UNTIL [NOT] EXISTS '(' query ')' ??
     REPEAT/DO/LOOP/... '(' sub-query-over-stuff ')'
   );

At least the 3 sub-queries are just standard queries, only the wrapping 
around (ITERATE ... WHILE/UNTIL ... REPEAT ...) is WITH specific, which is 
somehow better than having new separators in the query syntax itself. It 
is pretty relational inside, and procedural on the outside, the two levels 
are not mixed, which is the real win from my point of view.

ISTM that the key take away from the above discussion is to keep the 
overhead syntax in WITH, it should not be moved inside the query in any 
way, like adding REPLACE or WHILE or whatever there. This way there is 
minimal interference with future query syntax extensions, there is only a 
specific WITH-level 3-query construct with pretty explicit markers.

-- 
Fabien.