From 7a3e30e608a25800a1f7fdfaaca4da3f0ac0fb07 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 12 Aug 2006 02:52:06 +0000 Subject: 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 --- doc/src/sgml/ref/delete.sgml | 56 ++++++++++++++++++++++++++++++++++---- doc/src/sgml/ref/insert.sgml | 65 ++++++++++++++++++++++++++++++++++++++++---- doc/src/sgml/ref/update.sgml | 57 ++++++++++++++++++++++++++++++++++++-- 3 files changed, 164 insertions(+), 14 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml index b61e6cacd21..6acc01b5604 100644 --- a/doc/src/sgml/ref/delete.sgml +++ b/doc/src/sgml/ref/delete.sgml @@ -1,5 +1,5 @@ @@ -23,6 +23,7 @@ PostgreSQL documentation DELETE FROM [ ONLY ] table [ [ AS ] alias ] [ USING usinglist ] [ WHERE condition ] + [ RETURNING * | output_expression [ AS output_name ] [, ...] ] @@ -59,6 +60,15 @@ DELETE FROM [ ONLY ] table [ [ AS ] circumstances. + + The optional RETURNING clause causes DELETE + to compute and return value(s) based on each row actually deleted. + Any expression using the table's columns, and/or columns of other + tables mentioned in USING, can be computed. + The syntax of the RETURNING list is identical to that of the + output list of SELECT. + + You must have the DELETE privilege on the table to delete from it, as well as the SELECT @@ -130,6 +140,28 @@ DELETE FROM [ ONLY ] table [ [ AS ] + + + output_expression + + + An expression to be computed and returned by the DELETE + command after each row is deleted. The expression may use any + column names of the table + or table(s) listed in USING. + Write * to return all columns. + + + + + + output_name + + + A name to use for a returned column. + + + @@ -148,6 +180,14 @@ DELETE count class="parameter">condition (this is not considered an error). + + + If the DELETE command contains a RETURNING + clause, the result will be similar to that of a SELECT + statement containing the columns and values defined in the + RETURNING list, computed over the row(s) deleted by the + command. + @@ -189,6 +229,13 @@ DELETE FROM films WHERE kind <> 'Musical'; Clear the table films: DELETE FROM films; + + + + + Delete completed tasks, returning full details of the deleted rows: + +DELETE FROM tasks WHERE status = 'DONE' RETURNING *; @@ -197,10 +244,9 @@ DELETE FROM films; Compatibility - This command conforms to the SQL standard, except that the - USING clause and the ability to reference other tables - in the WHERE clause are PostgreSQL - extensions. + This command conforms to the SQL standard, except + that the USING and RETURNING clauses + are PostgreSQL extensions. diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index 4e589b599b6..55eaef08523 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -1,5 +1,5 @@ @@ -21,7 +21,8 @@ PostgreSQL documentation INSERT INTO table [ ( column [, ...] ) ] - { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query } + { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } + [ RETURNING * | output_expression [ AS output_name ] [, ...] ] @@ -30,8 +31,8 @@ INSERT INTO table [ ( INSERT inserts new rows into a table. - One can insert a single row specified by value expressions, - or several rows as a result of a query. + One can insert rows specified by value expressions, + or rows computed as a result of a query. @@ -55,6 +56,16 @@ INSERT INTO table [ ( + + The optional RETURNING clause causes INSERT + to compute and return value(s) based on each row actually inserted. + This is primarily useful for obtaining values that were supplied by + defaults, such as a serial sequence number. However, any expression + using the table's columns is allowed. The syntax of the + RETURNING list is identical to that of the output list + of SELECT. + + You must have INSERT privilege to a table in order to insert into it. If you use the table [ ( A query (SELECT statement) that supplies the - rows to be inserted. Refer to the SELECT + rows to be inserted. Refer to the + statement for a description of the syntax. + + + output_expression + + + An expression to be computed and returned by the INSERT + command after each row is inserted. The expression may use any + column names of the table. + Write * to return all columns of the inserted row(s). + + + + + + output_name + + + A name to use for a returned column. + + + @@ -147,6 +180,14 @@ INSERT oid countOID assigned to the inserted row. Otherwise oid is zero. + + + If the INSERT command contains a RETURNING + clause, the result will be similar to that of a SELECT + statement containing the columns and values defined in the + RETURNING list, computed over the row(s) inserted by the + command. + @@ -211,6 +252,16 @@ INSERT INTO tictactoe (game, board[1:3][1:3]) -- The subscripts in the above example aren't really needed INSERT INTO tictactoe (game, board) VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}'); + + + + + Insert a single row into table distributors, returning + the sequence number generated by the DEFAULT clause: + + +INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') + RETURNING did; @@ -219,7 +270,9 @@ INSERT INTO tictactoe (game, board) Compatibility - INSERT conforms to the SQL standard. The case in + INSERT conforms to the SQL standard, except that + the RETURNING clause is a + PostgreSQL extension. Also, the case in which a column name list is omitted, but not all the columns are filled from the VALUES clause or query, is disallowed by the standard. diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml index 3b03e86a1eb..5d1265e945f 100644 --- a/doc/src/sgml/ref/update.sgml +++ b/doc/src/sgml/ref/update.sgml @@ -1,5 +1,5 @@ @@ -24,6 +24,7 @@ UPDATE [ ONLY ] table [ [ AS ] column = { expression | DEFAULT } [, ...] [ FROM fromlist ] [ WHERE condition ] + [ RETURNING * | output_expression [ AS output_name ] [, ...] ] @@ -52,6 +53,16 @@ UPDATE [ ONLY ] table [ [ AS ] + + The optional RETURNING clause causes UPDATE + to compute and return value(s) based on each row actually updated. + Any expression using the table's columns, and/or columns of other + tables mentioned in FROM, can be computed. + The new (post-update) values of the table's columns are used. + The syntax of the RETURNING list is identical to that of the + output list of SELECT. + + You must have the UPDATE privilege on the table to update it, as well as the SELECT @@ -147,6 +158,28 @@ UPDATE [ ONLY ] table [ [ AS ] + + + output_expression + + + An expression to be computed and returned by the UPDATE + command after each row is updated. The expression may use any + column names of the table + or table(s) listed in FROM. + Write * to return all columns. + + + + + + output_name + + + A name to use for a returned column. + + + @@ -165,6 +198,14 @@ UPDATE count class="parameter">condition (this is not considered an error). + + + If the UPDATE command contains a RETURNING + clause, the result will be similar to that of a SELECT + statement containing the columns and values defined in the + RETURNING list, computed over the row(s) updated by the + command. + @@ -212,6 +253,16 @@ UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT + + Perform the same operation and return the updated entries: + + +UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT + WHERE city = 'San Francisco' AND date = '2003-07-03' + RETURNING temp_lo, temp_hi, prcp; + + + Increment the sales count of the salesperson who manages the account for Acme Corporation, using the FROM @@ -256,8 +307,8 @@ COMMIT; This command conforms to the SQL standard, except - that the FROM clause is a - PostgreSQL extension. + that the FROM and RETURNING clauses + are PostgreSQL extensions. -- cgit v1.2.3