<sect1 id="dml-returning">
<title>Returning Data from Modified Rows</title>
<indexterm zone="dml-returning">
<primary>RETURNING</primary>
</indexterm>
<indexterm zone="dml-returning">
<primary>INSERT</primary>
<secondary>RETURNING</secondary>
</indexterm>
<indexterm zone="dml-returning">
<primary>UPDATE</primary>
<secondary>RETURNING</secondary>
</indexterm>
<indexterm zone="dml-returning">
<primary>DELETE</primary>
<secondary>RETURNING</secondary>
</indexterm>
<indexterm zone="dml-returning">
<primary>MERGE</primary>
<secondary>RETURNING</secondary>
</indexterm>
<para>
Sometimes it is useful to obtain data from modified rows while they are
being manipulated. The <command>INSERT</command>, <command>UPDATE</command>,
<command>DELETE</command>, and <command>MERGE</command> commands all have an
optional <literal>RETURNING</literal> clause that supports this. Use
of <literal>RETURNING</literal> avoids performing an extra database query to
collect the data, and is especially valuable when it would otherwise be
difficult to identify the modified rows reliably.
</para>
<para>
The allowed contents of a <literal>RETURNING</literal> clause are the same as
a <command>SELECT</command> command's output list
(see <xref linkend="queries-select-lists"/>). It can contain column
names of the command's target table, or value expressions using those
columns. A common shorthand is <literal>RETURNING *</literal>, which selects
all columns of the target table in order.
</para>
<para>
In an <command>INSERT</command>, the default data available to
<literal>RETURNING</literal> is
the row as it was inserted. This is not so useful in trivial inserts,
since it would just repeat the data provided by the client. But it can
be very handy when relying on computed default values. For example,
when using a <link linkend="datatype-serial"><type>serial</type></link>
column to provide unique identifiers, <literal>RETURNING</literal> can return
the ID assigned to a new row:
<programlisting>
CREATE TABLE users (firstname text, lastname text, id serial primary key);
INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;
</programlisting>
The <literal>RETURNING</literal> clause is also very useful
with <literal>INSERT ... SELECT</literal>.
</para>
<para>
In an <command>UPDATE</command>, the default data available to
<literal>RETURNING</literal> is
the new content of the modified row. For example:
<programlisting>
UPDATE products SET price = price * 1.10
WHERE price <= 99.99
RETURNING name, price AS new_price;
</programlisting>
</para>
<para>
In a <command>DELETE</command>,