[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
Right now, our update function in esqueleto only supports updating a single table (no FROM clause allowed), and we don't have the ability to return anything from it aside from the count of rows modified.
A type and API
UPDATE ... RETURNING returns a list of rows that were actually updated, so it should have a return type like select.
The SqlQuery already allows us to do CTEs, but we'll have to be careful to structure them outside the UPDATE syntax.
The FROM stuff is a bit tricky. Per docs,
When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_item list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.
More FROM trickiness - we can't update non-target tables. So the type introduced by the lambda should be slightly different from the type introduced by from.
To account for the above, we want to do something like:
update
:: (SqlSelect a r, PersistEntity val, _)
=> (UpdateTarget val -> SqlQuery a)
-> SqlWriteT m [r]
UpdateTarget
This would be a new type for introducing the entity. Presumably, it would wrap SqlExpr somehow. An ideal design would be to retain transparency and compatibility with the old API and existing functions, while also providing type-safety for the UPDATE clause's SETs (which can only touch the target table).
We can simply have type UpdateTarget val = SqlExpr (Entity val) - aka, no distinction between "a table targeted for update" and "a table brought into scope via from." This would be the easiest thing to do - no modification to anything required really. It's also the least safe option.
Having it be a newtype wrapper of some sort allows us to provide: readUpdateTarget :: UpdateTarget val -> SqlExpr (Entity val). Then the user can do:
update $ \fooWrite -> do
let fooRead = readUpdateTarget fooWrite
set fooWrite [FooName =. val "hello", FooAge +=. val 1]
where_ $ fooRead ^. FooName ==. val "goodbye"
This combination maybe common enough that we'd want to just pass both in the lambda. But that may be confusing too.
We could generalize (^.) to accept either a SqlExpr (Entity val) or UpdateTarget val. But there'd be work to generalize this to all other cases, too.
This is going to be a mega issue that documents improving
updateforPostgresqlspecifically.Related issues:
updatedoesn't work with experimental syntaxWHEREtoinsertSelectOnConflictupdate ... returningPostgres Docs
These are the Postgres docs for
update. Copying the syntax, we have:Right now, our
updatefunction inesqueletoonly supports updating a single table (noFROMclause allowed), and we don't have the ability to return anything from it aside from the count of rows modified.A type and API
UPDATE ... RETURNINGreturns a list of rows that were actually updated, so it should have a return type likeselect.SqlQueryalready allows us to do CTEs, but we'll have to be careful to structure them outside theUPDATEsyntax.FROMstuff is a bit tricky. Per docs,FROMtrickiness - we can't update non-target tables. So the type introduced by the lambda should be slightly different from the type introduced byfrom.The current signature is:
To account for the above, we want to do something like:
UpdateTargetThis would be a new type for introducing the entity. Presumably, it would wrap
SqlExprsomehow. An ideal design would be to retain transparency and compatibility with the old API and existing functions, while also providing type-safety for theUPDATEclause'sSETs (which can only touch the target table).We can simply have
type UpdateTarget val = SqlExpr (Entity val)- aka, no distinction between "a table targeted for update" and "a table brought into scope viafrom." This would be the easiest thing to do - no modification to anything required really. It's also the least safe option.Having it be a
newtypewrapper of some sort allows us to provide:readUpdateTarget :: UpdateTarget val -> SqlExpr (Entity val). Then the user can do:This combination maybe common enough that we'd want to just pass both in the lambda. But that may be confusing too.
We could generalize
(^.)to accept either aSqlExpr (Entity val)orUpdateTarget val. But there'd be work to generalize this to all other cases, too.