Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7a3e30e

Browse files
committed
Add INSERT/UPDATE/DELETE RETURNING, with basic docs and regression tests.
plpgsql support to come later. Along the way, convert execMain'sSELECT INTO support into a DestReceiver, in order to eliminate some uglyspecial cases.Jonah Harris and Tom Lane
1 parent5c9e9c0 commit7a3e30e

File tree

35 files changed

+1459
-409
lines changed

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 inserta single row specified by value expressions,
34-
orseveralrows as a result of a query.
34+
One can insertrows 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-
* Ifthis is a retrieve, andwe are supposed to emit row descriptions,
131-
* thenwesend 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
returnExecCleanTypeFromTL(query->targetList, false);
449449

450+
casePORTAL_ONE_RETURNING:
451+
query= (Query*)linitial(stmt->query_list);
452+
returnExecCleanTypeFromTL(query->returningList, false);
453+
450454
casePORTAL_UTIL_SELECT:
451455
query= (Query*)linitial(stmt->query_list);
452456
returnUtilityTupleDescriptor(query->utilityStmt);
@@ -472,6 +476,7 @@ PreparedStatementReturnsTuples(PreparedStatement *stmt)
472476
switch (ChoosePortalStrategy(stmt->query_list))
473477
{
474478
casePORTAL_ONE_SELECT:
479+
casePORTAL_ONE_RETURNING:
475480
casePORTAL_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)
[8]ページ先頭
©2009-2025 Movatter.jp