Documentation

Statement represents a single PostgreSQL statement. Most commonly, this is constructed using select, insert, update or delete.

However, in addition to SELECT, INSERT, UPDATE and DELETE, PostgreSQL also supports compositions thereof via its statement-level WITH syntax (with some caveats). Each such "sub-statement" can reference the results of previous sub-statements. Statement provides a Monad instance that captures this "binding" pattern.

The caveat with this is that the side-effects of these sub-statements are not visible to other sub-statements; only the explicit results of previous sub-statements (from SELECTs or RETURNING clauses) are visible. So, for example, an INSERT into a table followed immediately by a SELECT therefrom will not return the inserted rows. However, it is possible to return the inserted rows using RETURNING, unionAlling this with the result of a SELECT from the same table will produce the desired result.

An example of where this can be useful is if you want to delete rows from a table and simultaneously log their deletion in a log table.

deleteFoo :: (Foo Expr -> Expr Bool) -> Statement ()
deleteFoo predicate = do
  foos <-
    delete Delete
      { from = fooSchema
      , using = pure ()
      , deleteWhere = \_ -> predicate
      , returning = Returning id
      }
  insert Insert
    { into = deletedFooSchema
    , rows = do
        Foo {..} <- foos
        let
          deletedAt = now
        pure DeletedFoo {..}
    , onConflict = Abort
    , returning = NoReturning
    }