git.iko.soy/rel8
on ae4607a help sign in
rel8/docs/concepts/insert.rst
181 lines 6.0 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``using``
25 This is a simple ``Query`` that forms the ``USING`` clause of the ``DELETE``
26 statement. This can be used to join against other tables, and the results
27 can be referenced in the ``deleteWhere`` parameter. For simple ``DELETE``\s
28 where you don't need to do this, you can set ``using = pure ()``.
30``deleteWhere``
31 The ``WHERE`` clause of the ``DELETE`` statement. This is a function that
32 takes two inputs: the result of the ``using`` query, and the current value
33 of the row.
35``returning``
36 What to return - see :ref:`returning`.
38``UPDATE``
39----------
41To perform a ``UPDATE`` statement, construct a ``Update`` value and execute it
42using ``update``. ``Update`` takes:
44``target``
45 The ``TableSchema`` for the table to update rows in.
47``from``
48 This is a simple ``Query`` that forms the ``FROM`` clause of the ``UPDATE``
49 statement. This can be used to join against other tables, and the results
50 can be referenced in the ``set`` and ``updateWhere`` parameters. For simple
51 ``UPDATE``\s where you don't need to do this, you can set ``from = pure ()``.
53``set``
54 A row to row transformation function, indicating how to update selected rows.
55 This function takes rows of the same shape as ``target`` but in the ``Expr``
56 context. One way to write this function is to use record update syntax::
58 set = \from row -> row { rowName = "new name" }
60``updateWhere``
61 The ``WHERE`` clause of the ``UPDATE`` statement. This is a function that
62 takes two inputs: the result of the ``from`` query, and the current value of
63 the row.
65``returning``
66 What to return - see :ref:`returning`.
68``INSERT``
69----------
71To perform a ``INSERT`` statement, construct a ``Insert`` value and execute it
72using ``insert``. ``Insert`` takes:
74``into``
75 The ``TableSchema`` for the table to insert rows into.
77``rows``
78 The rows to insert. These are the same as ``into``, but in the ``Expr``
79 context. You can construct rows from their individual fields::
81 rows = values [ MyTable { myTableA = lit "A", myTableB = lit 42 }
83 or you can use ``lit`` on a table value in the ``Result`` context::
85 rows = values [ lit MyTable { myTableA = "A", myTableB = 42 }
87``onConflict``
88 What should happen if an insert clashes with rows that already exist. This
89 corresponds to PostgreSQL's ``ON CONFLICT`` clause. You can specify:
91 ``Abort``
92 PostgreSQL should abort the ``INSERT`` with an exception
94 ``DoNothing``
95 PostgreSQL should not insert the duplicate rows.
97 ``DoUpdate``
98 PostgreSQL should instead try to update any existing rows that conflict
99 with rows proposed for insertion.
101``returning``
102 What to return - see :ref:`returning`.
104.. _returning:
106``RETURNING``
107-------------
109PostgreSQL has the ability to return extra information after a ``DELETE``,
110``INSERT`` or ``UPDATE`` statement by attaching a ``RETURNING`` clause. A common
111use of this clause is to return any automatically generated sequence values for
112primary key columns. Rel8 supports ``RETURNING`` clauses by filling in the
113``returning`` field and specifying a ``Projection``. A ``Projection`` is a row
114to row transformation, allowing you to project out a subset of fields.
116For example, if we are inserting orders, we might want the order ids returned::
118 insert Insert
119 { into = orderSchema
120 , rows = values [ order ]
121 , onConflict = Abort
122 , returning = Projection orderId
123 }
125If we don't want to return anything, we can use ``pure ()``::
127 insert Insert
128 { into = orderSchema
129 , rows = values [ order ]
130 , onConflict = Abort
131 , returning = pure ()
132 }
134Default values
135--------------
137It is fairly common to define tables with default values. While Rel8 does not
138have specific functionality for ``DEFAULT``, there are a few options:
140``unsafeDefault``
141~~~~~~~~~~~~~~~~~~~~~~~~~~~
143Rel8 does not have any special support for ``DEFAULT``. If you need to use
144default column values in inserts, you can use ``unsafeDefault`` to
145construct the ``DEFAULT`` expression::
147 insert Insert
148 { into = orderSchema
149 , rows = values [ Order { orderId = unsafeDefault, ... } ]
150 , onConflict = Abort
151 , returning = Projection orderId
152 }
154.. warning::
155 As the name suggests, this is an unsafe operation. In particular, Rel8 is not
156 able to prove that this column does have a default value, so it may be
157 possible to introduce a runtime error. Furthermore, ``DEFAULT`` is fairly
158 special in SQL, and cannot be combined like other expressions. For example,
159 the innocuous expression::
161 unsafeDefault + 1
163 will lead to a runtime crash.
165Reimplement default values in Rel8
166~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
168If you only need to access default values in Rel8, another option is to specify
169them in Rel8, rather than in your database schema.
171.. hint::
172 A common default value for primary keys is to use `nextval` to obtain the
173 next value from a sequence. This can be done in Rel8 by using the ``nextval``
174 function::
176 insert Insert
177 { into = orderSchema
178 , rows = values [ Order { orderId = nextval "order_id_seq", ... } ]
179 , onConflict = Abort
180 , returning = Projection orderId
181 }