The new syntax looks great, and I’m really excited to see this coming together.
One piece of feedback on the proposed syntax is around the use of “=“ as a
declaration in addition to its current use as an equality operator in a WHERE
clause and an assignment operator in an UPDATE:
BEGIN TRANSACTION
LET car_miles = miles_driven, car_is_running = is_running FROM cars WHERE
model=’pinto’
LET user_miles = miles_driven FROM users WHERE name=’blake’
SELECT something else from some other table
IF NOT car_is_running THEN ABORT
UPDATE users SET miles_driven = user_miles + 30 WHERE name='blake';
UPDATE cars SET miles_driven = car_miles + 30 WHERE model='pinto';
COMMIT TRANSACTION
This is supported in languages like PL/pgSQL, but in a normal SQL query kind of
local declaration is often expressed as an alias (SELECT col AS new_col),
subquery alias (SELECT col) t, or common table expression (WITH t AS (SELECT
col)).
Here’s an example of an alternative to the proposed syntax that I’d find more
readable:
BEGIN TRANSACTION
WITH car_miles, car_is_running AS (SELECT miles_driven, is_running FROM cars
WHERE model=’pinto’),
user_miles AS (SELECT miles_driven FROM users WHERE name=’blake’)
IF NOT car_is_running THEN ABORT
UPDATE users SET miles_driven = user_miles + 30 WHERE name='blake';
UPDATE cars SET miles_driven = car_miles + 30 WHERE model='pinto';
COMMIT TRANSACTION
There’s also the option of naming the transaction like a subquery, and
supporting LET via AS (this one I’m less sure about but wanted to propose
anyway):
BEGIN TRANSACTION t1
SELECT miles_driven AS t1.car_miles, is_running AS t1.car_is_running FROM
cars WHERE model=’pinto’;
SELECT miles_driven AS t1.user_miles FROM users WHERE name=’blake’;
IF NOT car_is_running THEN ABORT
UPDATE users SET miles_driven = user_miles + 30 WHERE name='blake';
UPDATE cars SET miles_driven = car_miles + 30 WHERE model='pinto';
COMMIT TRANSACTION
This also has the benefit of resolving ambiguity in case of naming conflicts
with existing (or future) column names.
--
Abe
