git.iko.soy/rel8
on 032242f help sign in
rel8/docs/concepts/insert.rst
154 lines 4.9 kB history raw
1``INSERT``, ``UPDATE`` and ``DELETE``
2=====================================
4While the majority of Rel8 is about building and executing ``SELECT``
5statement, Rel8 also has support for ``INSERT``, ``UPDATE`` and ``DELETE``.
6These statements are all executed using the ``insert``, ``update`` and
7``delete`` functions, all of which take a record of parameters.
9.. note::
11 This part of Rel8's API uses the ``DuplicateRecordFields`` language
12 extension. In code that needs to use this API, you should also enable this
13 language extension, or you may get errors about ambiguous field names.
15``DELETE``
16----------
18To perform a ``DELETE`` statement, construct a ``Delete`` value and execute it
19using ``delete``. ``Delete`` takes:
21``from``
22 The ``TableSchema`` for the table to delete rows from.
24``deleteWhere``
25 The ``WHERE`` clause of the ``DELETE`` statement. This is a function that
26 takes a single ``Expr`` table as input.
28``returning``
29 What to return - see :ref:`returning`.
31``UPDATE``
32----------
34To perform a ``UPDATE`` statement, construct a ``Update`` value and execute it
35using ``update``. ``Update`` takes:
37``target``
38 The ``TableSchema`` for the table to update rows in.
40``updateWhere``
41 The ``WHERE`` clause of the ``UPDATE`` statement. This is a function that
42 takes a single ``Expr`` table as input.
44``set``
45 A row to row transformation function, indicating how to update selected rows.
46 This function takes rows of the same shape as ``target`` but in the ``Expr``
47 context. One way to write this function is to use record update syntax::
49 set = \row -> row { rowName = "new name" }
51``returning``
52 What to return - see :ref:`returning`.
54``INSERT``
55----------
57To perform a ``INSERT`` statement, construct a ``Insert`` value and execute it
58using ``insert``. ``Insert`` takes:
60``into``
61 The ``TableSchema`` for the table to insert rows into.
63``rows``
64 The rows to insert. These are the same as ``into``, but in the ``Expr``
65 context. You can construct rows from their individual fields::
67 rows = [ MyTable { myTableA = lit "A", myTableB = lit 42 }
69 or you can use ``lit`` on a table value in the ``Result`` context::
71 rows = [ lit MyTable { myTableA = "A", myTableB = 42 }
73``onConflict``
74 What should happen if an insert clashes with rows that already exist. This
75 corresponds to PostgreSQL's ``ON CONFLICT`` clause. You can specify:
77 ``Abort``
78 PostgreSQL should abort the ``INSERT`` with an exception
80 ``DoNothing``
81 PostgreSQL should not insert the duplicate rows.
83``returning``
84 What to return - see :ref:`returning`.
86.. _returning:
88``RETURNING``
89-------------
91PostgreSQL has the ability to return extra information after a ``DELETE``,
92``INSERT`` or ``UPDATE`` statement by attaching a ``RETURNING`` clause. A common
93use of this clause is to return any automatically generated sequence values for
94primary key columns. Rel8 supports ``RETURNING`` clauses by filling in the
95``returning`` field and specifying a ``Projection``. A ``Projection`` is a row
96to row transformation, allowing you to project out a subset of fields.
98For example, if we are inserting orders, we might want the order ids returned::
100 insert Insert
101 { into = orderSchema
102 , rows = [ order ]
103 , onConflict = Abort
104 , returning = Projection orderId
105 }
107Default values
108--------------
110It is fairly common to define tables with default values. While Rel8 does not
111have specific functionality for ``DEFAULT``, there are a few options:
113``unsafeDefault``
114~~~~~~~~~~~~~~~~~~~~~~~~~~~
116Rel8 does not have any special support for ``DEFAULT``. If you need to use
117default column values in inserts, you can use ``unsafeDefault`` to
118construct the ``DEFAULT`` expression::
120 insert Insert
121 { into = orderSchema
122 , rows = [ Order { orderId = unsafeDefault, ... } ]
123 , onConflict = Abort
124 , returning = Projection orderId
125 }
127.. warning::
128 As the name suggests, this is an unsafe operation. In particular, Rel8 is not
129 able to prove that this column does have a default value, so it may be
130 possible to introduce a runtime error. Furthermore, ``DEFAULT`` is fairly
131 special in SQL, and cannot be combined like other expressions. For example,
132 the innocuous expression::
134 unsafeDefault + 1
136 will lead to a runtime crash.
138Reimplement default values in Rel8
139~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
141If you only need to access default values in Rel8, another option is to specify
142them in Rel8, rather than in your database schema.
144.. hint::
145 A common default value for primary keys is to use `nextval` to obtain the
146 next value from a sequence. This can be done in Rel8 by using the ``nextval``
147 function::
149 insert Insert
150 { into = orderSchema
151 , rows = [ Order { orderId = nextval "order_id_seq", ... } ]
152 , onConflict = Abort
153 , returning = Projection orderId
154 }