Skip to content

Commit 7a3e30e

Browse files
tglsfdctglsfdc
committed
Add INSERT/UPDATE/DELETE RETURNING, with basic docs and regression tests.
plpgsql support to come later. Along the way, convert execMain's SELECT INTO support into a DestReceiver, in order to eliminate some ugly special cases. Jonah Harris and Tom Lane
1 parent 5c9e9c0 commit 7a3e30e

35 files changed

+1459
-409
lines changed

‎doc/src/sgml/ref/delete.sgml

Lines changed: 51 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/delete.sgml,v 1.26 2006/01/22 05:20:33 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/delete.sgml,v 1.27 2006/08/12 02:52:03 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -23,6 +23,7 @@ PostgreSQL documentation
2323
DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
2424
[ USING <replaceable class="PARAMETER">usinglist</replaceable> ]
2525
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
26+
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
2627
</synopsis>
2728
</refsynopsisdiv>
2829

@@ -59,6 +60,15 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ]
5960
circumstances.
6061
</para>
6162

63+
<para>
64+
The optional <literal>RETURNING</> clause causes <command>DELETE</>
65+
to compute and return value(s) based on each row actually deleted.
66+
Any expression using the table's columns, and/or columns of other
67+
tables mentioned in <literal>USING</literal>, can be computed.
68+
The syntax of the <literal>RETURNING</> list is identical to that of the
69+
output list of <command>SELECT</>.
70+
</para>
71+
6272
<para>
6373
You must have the <literal>DELETE</literal> privilege on the table
6474
to delete from it, as well as the <literal>SELECT</literal>
@@ -130,6 +140,28 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ]
130140
</para>
131141
</listitem>
132142
</varlistentry>
143+
144+
<varlistentry>
145+
<term><replaceable class="PARAMETER">output_expression</replaceable></term>
146+
<listitem>
147+
<para>
148+
An expression to be computed and returned by the <command>DELETE</>
149+
command after each row is deleted. The expression may use any
150+
column names of the <replaceable class="PARAMETER">table</replaceable>
151+
or table(s) listed in <literal>USING</>.
152+
Write <literal>*</> to return all columns.
153+
</para>
154+
</listitem>
155+
</varlistentry>
156+
157+
<varlistentry>
158+
<term><replaceable class="PARAMETER">output_name</replaceable></term>
159+
<listitem>
160+
<para>
161+
A name to use for a returned column.
162+
</para>
163+
</listitem>
164+
</varlistentry>
133165
</variablelist>
134166
</refsect1>
135167

@@ -148,6 +180,14 @@ DELETE <replaceable class="parameter">count</replaceable>
148180
class="parameter">condition</replaceable> (this is not considered
149181
an error).
150182
</para>
183+
184+
<para>
185+
If the <command>DELETE</> command contains a <literal>RETURNING</>
186+
clause, the result will be similar to that of a <command>SELECT</>
187+
statement containing the columns and values defined in the
188+
<literal>RETURNING</> list, computed over the row(s) deleted by the
189+
command.
190+
</para>
151191
</refsect1>
152192

153193
<refsect1>
@@ -189,6 +229,13 @@ DELETE FROM films WHERE kind &lt;&gt; 'Musical';
189229
Clear the table <literal>films</literal>:
190230
<programlisting>
191231
DELETE FROM films;
232+
</programlisting>
233+
</para>
234+
235+
<para>
236+
Delete completed tasks, returning full details of the deleted rows:
237+
<programlisting>
238+
DELETE FROM tasks WHERE status = 'DONE' RETURNING *;
192239
</programlisting>
193240
</para>
194241
</refsect1>
@@ -197,10 +244,9 @@ DELETE FROM films;
197244
<title>Compatibility</title>
198245

199246
<para>
200-
This command conforms to the SQL standard, except that the
201-
<literal>USING</> clause and the ability to reference other tables
202-
in the <literal>WHERE</> clause are <productname>PostgreSQL</>
203-
extensions.
247+
This command conforms to the <acronym>SQL</acronym> standard, except
248+
that the <literal>USING</literal> and <literal>RETURNING</> clauses
249+
are <productname>PostgreSQL</productname> extensions.
204250
</para>
205251
</refsect1>
206252
</refentry>

‎doc/src/sgml/ref/insert.sgml

Lines changed: 59 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.30 2005/11/17 22:14:51 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.31 2006/08/12 02:52:03 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -21,7 +21,8 @@ PostgreSQL documentation
2121
<refsynopsisdiv>
2222
<synopsis>
2323
INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ]
24-
{ DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) | <replaceable class="PARAMETER">query</replaceable> }
24+
{ DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> }
25+
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
2526
</synopsis>
2627
</refsynopsisdiv>
2728

@@ -30,8 +31,8 @@ INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable
3031

3132
<para>
3233
<command>INSERT</command> inserts new rows into a table.
33-
One can insert a single row specified by value expressions,
34-
or several rows as a result of a query.
34+
One can insert rows specified by value expressions,
35+
or rows computed as a result of a query.
3536
</para>
3637

3738
<para>
@@ -55,6 +56,16 @@ INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable
5556
automatic type conversion will be attempted.
5657
</para>
5758

59+
<para>
60+
The optional <literal>RETURNING</> clause causes <command>INSERT</>
61+
to compute and return value(s) based on each row actually inserted.
62+
This is primarily useful for obtaining values that were supplied by
63+
defaults, such as a serial sequence number. However, any expression
64+
using the table's columns is allowed. The syntax of the
65+
<literal>RETURNING</> list is identical to that of the output list
66+
of <command>SELECT</>.
67+
</para>
68+
5869
<para>
5970
You must have <literal>INSERT</literal> privilege to a table in
6071
order to insert into it. If you use the <replaceable
@@ -123,11 +134,33 @@ INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable
123134
<listitem>
124135
<para>
125136
A query (<command>SELECT</command> statement) that supplies the
126-
rows to be inserted. Refer to the <command>SELECT</command>
137+
rows to be inserted. Refer to the
138+
<xref linkend="sql-select" endterm="sql-select-title">
127139
statement for a description of the syntax.
128140
</para>
129141
</listitem>
130142
</varlistentry>
143+
144+
<varlistentry>
145+
<term><replaceable class="PARAMETER">output_expression</replaceable></term>
146+
<listitem>
147+
<para>
148+
An expression to be computed and returned by the <command>INSERT</>
149+
command after each row is inserted. The expression may use any
150+
column names of the <replaceable class="PARAMETER">table</replaceable>.
151+
Write <literal>*</> to return all columns of the inserted row(s).
152+
</para>
153+
</listitem>
154+
</varlistentry>
155+
156+
<varlistentry>
157+
<term><replaceable class="PARAMETER">output_name</replaceable></term>
158+
<listitem>
159+
<para>
160+
A name to use for a returned column.
161+
</para>
162+
</listitem>
163+
</varlistentry>
131164
</variablelist>
132165
</refsect1>
133166

@@ -147,6 +180,14 @@ INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</repl
147180
<acronym>OID</acronym> assigned to the inserted row. Otherwise
148181
<replaceable class="parameter">oid</replaceable> is zero.
149182
</para>
183+
184+
<para>
185+
If the <command>INSERT</> command contains a <literal>RETURNING</>
186+
clause, the result will be similar to that of a <command>SELECT</>
187+
statement containing the columns and values defined in the
188+
<literal>RETURNING</> list, computed over the row(s) inserted by the
189+
command.
190+
</para>
150191
</refsect1>
151192

152193
<refsect1>
@@ -211,6 +252,16 @@ INSERT INTO tictactoe (game, board[1:3][1:3])
211252
-- The subscripts in the above example aren't really needed
212253
INSERT INTO tictactoe (game, board)
213254
VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');
255+
</programlisting>
256+
</para>
257+
258+
<para>
259+
Insert a single row into table <literal>distributors</literal>, returning
260+
the sequence number generated by the <literal>DEFAULT</literal> clause:
261+
262+
<programlisting>
263+
INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
264+
RETURNING did;
214265
</programlisting>
215266
</para>
216267
</refsect1>
@@ -219,7 +270,9 @@ INSERT INTO tictactoe (game, board)
219270
<title>Compatibility</title>
220271

221272
<para>
222-
<command>INSERT</command> conforms to the SQL standard. The case in
273+
<command>INSERT</command> conforms to the SQL standard, except that
274+
the <literal>RETURNING</> clause is a
275+
<productname>PostgreSQL</productname> extension. Also, the case in
223276
which a column name list is omitted, but not all the columns are
224277
filled from the <literal>VALUES</> clause or <replaceable>query</>,
225278
is disallowed by the standard.

‎doc/src/sgml/ref/update.sgml

Lines changed: 54 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.37 2006/03/08 22:59:09 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.38 2006/08/12 02:52:03 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -24,6 +24,7 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep
2424
SET <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...]
2525
[ FROM <replaceable class="PARAMETER">fromlist</replaceable> ]
2626
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
27+
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ AS <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
2728
</synopsis>
2829
</refsynopsisdiv>
2930

@@ -52,6 +53,16 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep
5253
circumstances.
5354
</para>
5455

56+
<para>
57+
The optional <literal>RETURNING</> clause causes <command>UPDATE</>
58+
to compute and return value(s) based on each row actually updated.
59+
Any expression using the table's columns, and/or columns of other
60+
tables mentioned in <literal>FROM</literal>, can be computed.
61+
The new (post-update) values of the table's columns are used.
62+
The syntax of the <literal>RETURNING</> list is identical to that of the
63+
output list of <command>SELECT</>.
64+
</para>
65+
5566
<para>
5667
You must have the <literal>UPDATE</literal> privilege on the table
5768
to update it, as well as the <literal>SELECT</literal>
@@ -147,6 +158,28 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep
147158
</para>
148159
</listitem>
149160
</varlistentry>
161+
162+
<varlistentry>
163+
<term><replaceable class="PARAMETER">output_expression</replaceable></term>
164+
<listitem>
165+
<para>
166+
An expression to be computed and returned by the <command>UPDATE</>
167+
command after each row is updated. The expression may use any
168+
column names of the <replaceable class="PARAMETER">table</replaceable>
169+
or table(s) listed in <literal>FROM</>.
170+
Write <literal>*</> to return all columns.
171+
</para>
172+
</listitem>
173+
</varlistentry>
174+
175+
<varlistentry>
176+
<term><replaceable class="PARAMETER">output_name</replaceable></term>
177+
<listitem>
178+
<para>
179+
A name to use for a returned column.
180+
</para>
181+
</listitem>
182+
</varlistentry>
150183
</variablelist>
151184
</refsect1>
152185

@@ -165,6 +198,14 @@ UPDATE <replaceable class="parameter">count</replaceable>
165198
class="parameter">condition</replaceable> (this is not considered
166199
an error).
167200
</para>
201+
202+
<para>
203+
If the <command>UPDATE</> command contains a <literal>RETURNING</>
204+
clause, the result will be similar to that of a <command>SELECT</>
205+
statement containing the columns and values defined in the
206+
<literal>RETURNING</> list, computed over the row(s) updated by the
207+
command.
208+
</para>
168209
</refsect1>
169210

170211
<refsect1>
@@ -212,6 +253,16 @@ UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
212253
</programlisting>
213254
</para>
214255

256+
<para>
257+
Perform the same operation and return the updated entries:
258+
259+
<programlisting>
260+
UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
261+
WHERE city = 'San Francisco' AND date = '2003-07-03'
262+
RETURNING temp_lo, temp_hi, prcp;
263+
</programlisting>
264+
</para>
265+
215266
<para>
216267
Increment the sales count of the salesperson who manages the
217268
account for Acme Corporation, using the <literal>FROM</literal>
@@ -256,8 +307,8 @@ COMMIT;
256307

257308
<para>
258309
This command conforms to the <acronym>SQL</acronym> standard, except
259-
that the <literal>FROM</literal> clause is a
260-
<productname>PostgreSQL</productname> extension.
310+
that the <literal>FROM</literal> and <literal>RETURNING</> clauses
311+
are <productname>PostgreSQL</productname> extensions.
261312
</para>
262313

263314
<para>

‎src/backend/access/common/printtup.c

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
* Portions Copyright (c) 1994, Regents of the University of California
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/access/common/printtup.c,v 1.97 2006/07/14 14:52:16 momjian Exp $
12+
* $PostgreSQL: pgsql/src/backend/access/common/printtup.c,v 1.98 2006/08/12 02:52:03 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -127,10 +127,10 @@ printtup_startup(DestReceiver *self, int operation, TupleDesc typeinfo)
127127
}
128128

129129
/*
130-
* If this is a retrieve, and we are supposed to emit row descriptions,
131-
* then we send back the tuple descriptor of the tuples.
130+
* If we are supposed to emit row descriptions,
131+
* then send the tuple descriptor of the tuples.
132132
*/
133-
if (operation == CMD_SELECT && myState->sendDescrip)
133+
if (myState->sendDescrip)
134134
SendRowDescriptionMessage(typeinfo,
135135
FetchPortalTargetList(portal),
136136
portal->formats);

‎src/backend/commands/prepare.c

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
* Copyright (c) 2002-2006, PostgreSQL Global Development Group
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.59 2006/08/08 01:23:15 momjian Exp $
13+
* $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.60 2006/08/12 02:52:04 tgl Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -447,6 +447,10 @@ FetchPreparedStatementResultDesc(PreparedStatement *stmt)
447447
query = (Query *) linitial(stmt->query_list);
448448
return ExecCleanTypeFromTL(query->targetList, false);
449449

450+
case PORTAL_ONE_RETURNING:
451+
query = (Query *) linitial(stmt->query_list);
452+
return ExecCleanTypeFromTL(query->returningList, false);
453+
450454
case PORTAL_UTIL_SELECT:
451455
query = (Query *) linitial(stmt->query_list);
452456
return UtilityTupleDescriptor(query->utilityStmt);
@@ -472,6 +476,7 @@ PreparedStatementReturnsTuples(PreparedStatement *stmt)
472476
switch (ChoosePortalStrategy(stmt->query_list))
473477
{
474478
case PORTAL_ONE_SELECT:
479+
case PORTAL_ONE_RETURNING:
475480
case PORTAL_UTIL_SELECT:
476481
return true;
477482

@@ -499,6 +504,8 @@ FetchPreparedStatementTargetList(PreparedStatement *stmt)
499504

500505
if (strategy == PORTAL_ONE_SELECT)
501506
return ((Query *) linitial(stmt->query_list))->targetList;
507+
if (strategy == PORTAL_ONE_RETURNING)
508+
return ((Query *) linitial(stmt->query_list))->returningList;
502509
if (strategy == PORTAL_UTIL_SELECT)
503510
{
504511
Node *utilityStmt;

0 commit comments

Comments
 (0)