Improved Postgres Update

[parsonsmatt]

This is going to be a mega issue that documents improving update for Postgresql specifically.

Related issues:

• update doesn't work with Experimental syntax #251 update doesn't work with experimental syntax
• Apply WHERE clause to INSERT SELECT .. ON CONFLICT #204 Add a WHERE to insertSelectOnConflict
• Support UPDATE ... RETURNING #44 Support update ... returning

Postgres Docs

These are the Postgres docs for update. Copying the syntax, we have:

[ 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.

The current signature is:

update :: (SqlExpr (Entity val) -> SqlQuery ()) -> SqlWriteT m ()

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.

[parsonsmatt]

#396 mentioned that postgres also supports WITH, so we should try and figure that out as well.