Hello Corey, Hello Peter,
My 0.02 € about the alternative syntaxes:
Peter:
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
)
A good point about this approach is that the replacement semantics is
clear, whereas using ITERATIVE with UNION is very misleading, as it is
*not* a union at all.
This said I'm wondering how the parser would react.
Moreover, having a different syntax for normal queries and inside WITH
query looks very undesirable from a language design point of view. This
suggests that the user should be able to write it anywhere:
SELECT 1 REPLACE SELECT 2;
Well, maybe.
I'm unclear whether "REPLACE ALL" vs "REPLACE" makes sense, ISTM that
there could be only one replacement semantics (delete the content and
insert a new one)?
REPLACE should have an associativity defined wrt other operators:
SELECT 1 UNION SELECT 2 REPLACE SELECT 3; -- how many rows?
I do not see anything obvious. Probably 2 rows.
Corey:
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.
Indeed!
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.
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?
ISTM that the WHILE would be checked before updating, so that WHILE FALSE
does nothing, in which case its position after SET is odd.
Having both WHERE and WHILE might look awkward.
Also it looks much more procedural this way, which is the point, but also
depart from the declarative SELECT approach of WITH RECURSIVE.
--
Fabien.
