`INSERT`, `UPDATE` and `DELETE`¶

While the majority of Rel8 is about building and executing SELECT statement, Rel8 also has support for INSERT, UPDATE and DELETE. These statements are all executed using the insert, update and delete functions, all of which take a record of parameters.

Note

This part of Rel8’s API uses the DuplicateRecordFields language extension. In code that needs to use this API, you should also enable this language extension, or you may get errors about ambiguous field names.

`DELETE`¶

To perform a DELETE statement, construct a Delete value and execute it using delete. Delete takes:

from: The TableSchema for the table to delete rows from.
using: This is a simple Query that forms the USING clause of the DELETE statement. This can be used to join against other tables, and the results can be referenced in the deleteWhere parameter. For simple DELETEs where you don’t need to do this, you can set using = pure ().
deleteWhere: The WHERE clause of the DELETE statement. This is a function that takes two inputs: the result of the using query, and the current value of the row.
returning: What to return - see RETURNING.

`UPDATE`¶

To perform a UPDATE statement, construct a Update value and execute it using update. Update takes:

target

The TableSchema for the table to update rows in.

from

This is a simple Query that forms the FROM clause of the UPDATE statement. This can be used to join against other tables, and the results can be referenced in the set and updateWhere parameters. For simple UPDATEs where you don’t need to do this, you can set from = pure ().

set

A row to row transformation function, indicating how to update selected rows. This function takes rows of the same shape as target but in the Expr context. One way to write this function is to use record update syntax:

set = \from row -> row { rowName = "new name" }

updateWhere

The WHERE clause of the UPDATE statement. This is a function that takes two inputs: the result of the from query, and the current value of the row.

returning

What to return - see RETURNING.

`INSERT`¶

To perform a INSERT statement, construct a Insert value and execute it using insert. Insert takes:

into

The TableSchema for the table to insert rows into.

rows

The rows to insert. These are the same as into, but in the Expr context. You can construct rows from their individual fields:

rows = values [ MyTable { myTableA = lit "A", myTableB = lit 42 }

or you can use lit on a table value in the Result context:

rows = values [ lit MyTable { myTableA = "A", myTableB = 42 }

onConflict

What should happen if an insert clashes with rows that already exist. This corresponds to PostgreSQL’s ON CONFLICT clause. You can specify:

Abort: PostgreSQL should abort the INSERT with an exception
DoNothing: PostgreSQL should not insert the duplicate rows.
DoUpdate: PostgreSQL should instead try to update any existing rows that conflict with rows proposed for insertion.

returning

What to return - see RETURNING.

`RETURNING`¶

PostgreSQL has the ability to return extra information after a DELETE, INSERT or UPDATE statement by attaching a RETURNING clause. A common use of this clause is to return any automatically generated sequence values for primary key columns. Rel8 supports RETURNING clauses by filling in the returning field and specifying a Projection. A Projection is a row to row transformation, allowing you to project out a subset of fields.

For example, if we are inserting orders, we might want the order ids returned:

insert Insert
  { into = orderSchema
  , rows = values [ order ]
  , onConflict = Abort
  , returning = Projection orderId
  }

If we don’t want to return anything, we can use pure ():

insert Insert
  { into = orderSchema
  , rows = values [ order ]
  , onConflict = Abort
  , returning = pure ()
  }

Default values¶

It is fairly common to define tables with default values. While Rel8 does not have specific functionality for DEFAULT, there are a few options:

`unsafeDefault`¶

Rel8 does not have any special support for DEFAULT. If you need to use default column values in inserts, you can use unsafeDefault to construct the DEFAULT expression:

insert Insert
  { into = orderSchema
  , rows = values [ Order { orderId = unsafeDefault, ... } ]
  , onConflict = Abort
  , returning = Projection orderId
  }

Warning

As the name suggests, this is an unsafe operation. In particular, Rel8 is not able to prove that this column does have a default value, so it may be possible to introduce a runtime error. Furthermore, DEFAULT is fairly special in SQL, and cannot be combined like other expressions. For example, the innocuous expression:

unsafeDefault + 1

will lead to a runtime crash.

Reimplement default values in Rel8¶

If you only need to access default values in Rel8, another option is to specify them in Rel8, rather than in your database schema.

Hint

A common default value for primary keys is to use nextval to obtain the next value from a sequence. This can be done in Rel8 by using the nextval function:

insert Insert
  { into = orderSchema
  , rows = values [ Order { orderId = nextval "order_id_seq", ... } ]
  , onConflict = Abort
  , returning = Projection orderId
  }

INSERT, UPDATE and DELETE¶

DELETE¶

UPDATE¶

INSERT¶

RETURNING¶