Skip to content

Commit 1c7861e

Browse files
tglsfdctglsfdc
committed
Doc: add a section in Part II concerning RETURNING.
There are assorted references to RETURNING in Part II, but nothing that would qualify as an explanation of the feature, which seems like an oversight considering how useful it is. Add something. Noted while looking for a place to point a cross-reference to ...
1 parent e2a0ee6 commit 1c7861e

File tree

2 files changed

+101
-1
lines changed

2 files changed

+101
-1
lines changed

‎doc/src/sgml/dml.sgml

Lines changed: 99 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -102,6 +102,18 @@ INSERT INTO products (product_no, name, price) VALUES
102102
</programlisting>
103103
</para>
104104

105+
<para>
106+
It is also possible to insert the result of a query (which might be no
107+
rows, one row, or many rows):
108+
<programlisting>
109+
INSERT INTO products (product_no, name, price)
110+
SELECT product_no, name, price FROM new_products
111+
WHERE release_date = 'today';
112+
</programlisting>
113+
This provides the full power of the SQL query mechanism (<xref
114+
linkend="queries">) for computing the rows to be inserted.
115+
</para>
116+
105117
<tip>
106118
<para>
107119
When inserting a lot of data at the same time, considering using
@@ -252,4 +264,91 @@ DELETE FROM products;
252264
then all rows in the table will be deleted! Caveat programmer.
253265
</para>
254266
</sect1>
267+
268+
<sect1 id="dml-returning">
269+
<title>Returning Data From Modified Rows</title>
270+
271+
<indexterm zone="dml-returning">
272+
<primary>RETURNING</primary>
273+
</indexterm>
274+
275+
<indexterm zone="dml-returning">
276+
<primary>INSERT</primary>
277+
<secondary>RETURNING</secondary>
278+
</indexterm>
279+
280+
<indexterm zone="dml-returning">
281+
<primary>UPDATE</primary>
282+
<secondary>RETURNING</secondary>
283+
</indexterm>
284+
285+
<indexterm zone="dml-returning">
286+
<primary>DELETE</primary>
287+
<secondary>RETURNING</secondary>
288+
</indexterm>
289+
290+
<para>
291+
Sometimes it is useful to obtain data from modified rows while they are
292+
being manipulated. The <command>INSERT</>, <command>UPDATE</>,
293+
and <command>DELETE</> commands all have an
294+
optional <literal>RETURNING</> clause that supports this. Use
295+
of <literal>RETURNING</> avoids performing an extra database query to
296+
collect the data, and is especially valuable when it would otherwise be
297+
difficult to identify the modified rows reliably.
298+
</para>
299+
300+
<para>
301+
The allowed contents of a <literal>RETURNING</> clause are the same as
302+
a <command>SELECT</> command's output list
303+
(see <xref linkend="queries-select-lists">). It can contain column
304+
names of the command's target table, or value expressions using those
305+
columns. A common shorthand is <literal>RETURNING *</>, which selects
306+
all columns of the target table in order.
307+
</para>
308+
309+
<para>
310+
In an <command>INSERT</>, the data available to <literal>RETURNING</> is
311+
the row as it was inserted. This is not so useful in trivial inserts,
312+
since it would just repeat the data provided by the client. But it can
313+
be very handy when relying on computed default values. For example,
314+
when using a <link linkend="datatype-serial"><type>serial</></link>
315+
column to provide unique identifiers, <literal>RETURNING</> can return
316+
the ID assigned to a new row:
317+
<programlisting>
318+
CREATE TABLE users (firstname text, lastname text, id serial primary key);
319+
320+
INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;
321+
</programlisting>
322+
The <literal>RETURNING</> clause is also very useful
323+
with <literal>INSERT ... SELECT</>.
324+
</para>
325+
326+
<para>
327+
In an <command>UPDATE</>, the data available to <literal>RETURNING</> is
328+
the new content of the modified row. For example:
329+
<programlisting>
330+
UPDATE products SET price = price * 1.10
331+
WHERE price &lt;= 99.99
332+
RETURNING name, price AS new_price;
333+
</programlisting>
334+
</para>
335+
336+
<para>
337+
In a <command>DELETE</>, the data available to <literal>RETURNING</> is
338+
the content of the deleted row. For example:
339+
<programlisting>
340+
DELETE FROM products
341+
WHERE obsoletion_date = 'today'
342+
RETURNING *;
343+
</programlisting>
344+
</para>
345+
346+
<para>
347+
If there are triggers (<xref linkend="triggers">) on the target table,
348+
the data available to <literal>RETURNING</> is the row as modified by
349+
the triggers. Thus, inspecting columns computed by triggers is another
350+
common use-case for <literal>RETURNING</>.
351+
</para>
352+
353+
</sect1>
255354
</chapter>

‎doc/src/sgml/queries.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2262,7 +2262,8 @@ SELECT * FROM moved_rows;
22622262

22632263
<para>
22642264
Data-modifying statements in <literal>WITH</> usually have
2265-
<literal>RETURNING</> clauses, as seen in the example above.
2265+
<literal>RETURNING</> clauses (see <xref linkend="dml-returning">),
2266+
as shown in the example above.
22662267
It is the output of the <literal>RETURNING</> clause, <emphasis>not</> the
22672268
target table of the data-modifying statement, that forms the temporary
22682269
table that can be referred to by the rest of the query. If a

0 commit comments

Comments
 (0)