diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml
index 5d2ad3f..7acb862 100644
--- a/doc/src/sgml/ref/delete.sgml
+++ b/doc/src/sgml/ref/delete.sgml
@@ -25,6 +25,8 @@ PostgreSQL documentation
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
[ USING using_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
+ [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
+ [ LIMIT { count | ALL } ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
@@ -56,6 +58,24 @@ DELETE FROM [ ONLY ] table_name [ *
+ If the ORDER BY clause is present, the rows will be
+ processed in the specified order. In its absence, rows are
+ processed in whichever order the system finds fastest to
+ produce.
+
+
+
+ If the LIMIT (or FETCH FIRST) clause
+ is present, processing will stop after the system has attempted
+ to delete the specified amount of rows. In particular, if a row
+ was concurrently changed not to match the given WHERE
+ clause, it will count towards the LIMIT despite it
+ not being actually deleted. Unlike in SELECT, the
+ OFFSET clause is not available in
+ DELETE.
+
+
+
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
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml
index 90b9208..ea75e55 100644
--- a/doc/src/sgml/ref/update.sgml
+++ b/doc/src/sgml/ref/update.sgml
@@ -27,6 +27,8 @@ UPDATE [ ONLY ] table_name [ * ] [
( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
[ FROM from_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
+ [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
+ [ LIMIT { count | ALL } ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
@@ -50,6 +52,24 @@ UPDATE [ ONLY ] table_name [ * ] [
+ If the ORDER BY clause is present, the rows will be
+ processed in the specified order. In its absence, rows are
+ processed in whichever order the system finds fastest to
+ produce.
+
+
+
+ If the LIMIT (or FETCH FIRST) clause
+ is present, processing will stop after the system has attempted
+ to update the specified amount of rows. In particular, if a row
+ was concurrently changed not to match the given WHERE
+ clause, it will count towards the LIMIT despite it
+ not being actually updated. Unlike in SELECT, the
+ OFFSET clause is not available in
+ UPDATE.
+
+
+
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
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index c89d808..cf9e66d 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2516,6 +2516,8 @@ _copyDeleteStmt(const DeleteStmt *from)
COPY_NODE_FIELD(relation);
COPY_NODE_FIELD(usingClause);
COPY_NODE_FIELD(whereClause);
+ COPY_NODE_FIELD(sortClause);
+ COPY_NODE_FIELD(limitClause);
COPY_NODE_FIELD(returningList);
COPY_NODE_FIELD(withClause);
@@ -2531,6 +2533,8 @@ _copyUpdateStmt(const UpdateStmt *from)
COPY_NODE_FIELD(targetList);
COPY_NODE_FIELD(whereClause);
COPY_NODE_FIELD(fromClause);
+ COPY_NODE_FIELD(sortClause);
+ COPY_NODE_FIELD(limitClause);
COPY_NODE_FIELD(returningList);
COPY_NODE_FIELD(withClause);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 9793cf5..aa7f203 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -894,6 +894,8 @@ _equalDeleteStmt(const DeleteStmt *a, const DeleteStmt *b)
COMPARE_NODE_FIELD(relation);
COMPARE_NODE_FIELD(usingClause);
COMPARE_NODE_FIELD(whereClause);
+ COMPARE_NODE_FIELD(sortClause);
+ COMPARE_NODE_FIELD(limitClause);
COMPARE_NODE_FIELD(returningList);
COMPARE_NODE_FIELD(withClause);
@@ -907,6 +909,8 @@ _equalUpdateStmt(const UpdateStmt *a, const UpdateStmt *b)
COMPARE_NODE_FIELD(targetList);
COMPARE_NODE_FIELD(whereClause);
COMPARE_NODE_FIELD(fromClause);
+ COMPARE_NODE_FIELD(sortClause);
+ COMPARE_NODE_FIELD(limitClause);
COMPARE_NODE_FIELD(returningList);
COMPARE_NODE_FIELD(withClause);
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 123f2a6..4f2a918 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -2970,6 +2970,10 @@ raw_expression_tree_walker(Node *node,
return true;
if (walker(stmt->whereClause, context))
return true;
+ if (walker(stmt->sortClause, context))
+ return true;
+ if (walker(stmt->limitClause, context))
+ return true;
if (walker(stmt->returningList, context))
return true;
if (walker(stmt->withClause, context))
@@ -2988,6 +2992,10 @@ raw_expression_tree_walker(Node *node,
return true;
if (walker(stmt->fromClause, context))
return true;
+ if (walker(stmt->sortClause, context))
+ return true;
+ if (walker(stmt->limitClause, context))
+ return true;
if (walker(stmt->returningList, context))
return true;
if (walker(stmt->withClause, context))
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 7225bb6..b402892 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -386,6 +386,21 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt)
qual = transformWhereClause(pstate, stmt->whereClause,
EXPR_KIND_WHERE, "WHERE");
+ /*
+ * Transform the sort clause. It might need to add items to our target
+ * list, but that's fine.
+ */
+ qry->targetList = NIL;
+ qry->sortClause = transformSortClause(pstate,
+ stmt->sortClause,
+ &qry->targetList,
+ EXPR_KIND_ORDER_BY,
+ true,
+ false);
+
+ qry->limitCount = transformLimitClause(pstate, stmt->limitClause,
+ EXPR_KIND_LIMIT, "LIMIT");
+
qry->returningList = transformReturningList(pstate, stmt->returningList);
/* done building the range table and jointree */
@@ -1941,8 +1956,27 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
nsitem->p_lateral_only = false;
nsitem->p_lateral_ok = true;
- qry->targetList = transformTargetList(pstate, stmt->targetList,
- EXPR_KIND_UPDATE_SOURCE);
+ /*
+ * Transform the sort clause and the target list. As stmt->targetList
+ * contains the SET expressions and not actual columns of the underlying
+ * table, we have to transform the ORDER BY first, potentially adding
+ * resjunk columns to the target list. Afterwards the actual SET
+ * expressions from stmt->targetList are added to the end of the target
+ * list.
+ */
+ qry->targetList = NIL;
+ qry->sortClause = transformSortClause(pstate,
+ stmt->sortClause,
+ &qry->targetList,
+ EXPR_KIND_ORDER_BY,
+ true,
+ false);
+ qry->targetList = list_concat(qry->targetList,
+ transformTargetList(pstate,
+ stmt->targetList,
+ EXPR_KIND_UPDATE_SOURCE));
+ qry->limitCount = transformLimitClause(pstate, stmt->limitClause,
+ EXPR_KIND_LIMIT, "LIMIT");
qual = transformWhereClause(pstate, stmt->whereClause,
EXPR_KIND_WHERE, "WHERE");
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 81169a4..7338d61 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -388,7 +388,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type opt_column event cursor_options opt_hold opt_set_data
%type reindex_type drop_type comment_type security_label_type
-%type fetch_args limit_clause select_limit_value
+%type fetch_args limit_clause opt_limit_clause select_limit_value
offset_clause select_offset_value
select_offset_value2 opt_select_fetch_first_value
%type row_or_rows first_or_next
@@ -9121,13 +9121,15 @@ returning_clause:
*****************************************************************************/
DeleteStmt: opt_with_clause DELETE_P FROM relation_expr_opt_alias
- using_clause where_or_current_clause returning_clause
+ using_clause where_or_current_clause opt_sort_clause opt_limit_clause returning_clause
{
DeleteStmt *n = makeNode(DeleteStmt);
n->relation = $4;
n->usingClause = $5;
n->whereClause = $6;
- n->returningList = $7;
+ n->sortClause = $7;
+ n->limitClause = $8;
+ n->returningList = $9;
n->withClause = $1;
$$ = (Node *)n;
}
@@ -9187,6 +9189,8 @@ UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias
SET set_clause_list
from_clause
where_or_current_clause
+ opt_sort_clause
+ opt_limit_clause
returning_clause
{
UpdateStmt *n = makeNode(UpdateStmt);
@@ -9194,7 +9198,9 @@ UpdateStmt: opt_with_clause UPDATE relation_expr_opt_alias
n->targetList = $5;
n->fromClause = $6;
n->whereClause = $7;
- n->returningList = $8;
+ n->sortClause = $8;
+ n->limitClause = $9;
+ n->returningList = $10;
n->withClause = $1;
$$ = (Node *)n;
}
@@ -9700,6 +9706,11 @@ limit_clause:
{ $$ = $3; }
;
+opt_limit_clause:
+ limit_clause { $$ = $1; }
+ | /* EMPTY */ { $$ = NULL; }
+ ;
+
offset_clause:
OFFSET select_offset_value
{ $$ = $2; }
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index b5011af..ecf634d 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1043,6 +1043,8 @@ typedef struct DeleteStmt
RangeVar *relation; /* relation to delete from */
List *usingClause; /* optional using clause for more tables */
Node *whereClause; /* qualifications */
+ List *sortClause; /* ORDER BY */
+ Node *limitClause; /* LIMIT */
List *returningList; /* list of expressions to return */
WithClause *withClause; /* WITH clause */
} DeleteStmt;
@@ -1058,6 +1060,8 @@ typedef struct UpdateStmt
List *targetList; /* the target list (of ResTarget) */
Node *whereClause; /* qualifications */
List *fromClause; /* optional from clause for more tables */
+ List *sortClause; /* ORDER BY */
+ Node *limitClause; /* LIMIT */
List *returningList; /* list of expressions to return */
WithClause *withClause; /* WITH clause */
} UpdateStmt;
diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out
index 71b856f..2f88190 100644
--- a/src/test/regress/expected/update.out
+++ b/src/test/regress/expected/update.out
@@ -40,6 +40,65 @@ SELECT * FROM update_test;
10 | 20 |
(2 rows)
+-- test ORDER BY
+EXPLAIN (COSTS OFF) UPDATE update_test SET b = 10 ORDER BY b;
+ QUERY PLAN
+-------------------------------------
+ Update on update_test
+ -> Sort
+ Sort Key: b
+ -> Seq Scan on update_test
+(4 rows)
+
+EXPLAIN (COSTS OFF) UPDATE update_test t SET b = 10 ORDER BY t.b;
+ QUERY PLAN
+---------------------------------------
+ Update on update_test t
+ -> Sort
+ Sort Key: b
+ -> Seq Scan on update_test t
+(4 rows)
+
+EXPLAIN (COSTS OFF) UPDATE update_test SET b = 10 ORDER BY columndoesnotexist;
+ERROR: column "columndoesnotexist" does not exist
+LINE 1: ...COSTS OFF) UPDATE update_test SET b = 10 ORDER BY columndoes...
+ ^
+EXPLAIN (COSTS OFF) UPDATE update_test t SET b = 10 ORDER BY t.b + 0;
+ QUERY PLAN
+---------------------------------------
+ Update on update_test t
+ -> Sort
+ Sort Key: ((b + 0))
+ -> Seq Scan on update_test t
+(4 rows)
+
+EXPLAIN (COSTS OFF) UPDATE update_test t SET b = 20 WHERE a = 10 ORDER BY t.b + 0;
+ QUERY PLAN
+---------------------------------------
+ Update on update_test t
+ -> Sort
+ Sort Key: ((b + 0))
+ -> Seq Scan on update_test t
+ Filter: (a = 10)
+(5 rows)
+
+EXPLAIN (COSTS OFF) UPDATE update_test t1 SET b = 20 FROM update_test t2 WHERE t1.a = t2.a ORDER BY t2.b;
+ QUERY PLAN
+----------------------------------------------------
+ Update on update_test t1
+ -> Sort
+ Sort Key: t2.b
+ -> Merge Join
+ Merge Cond: (t1.a = t2.a)
+ -> Sort
+ Sort Key: t1.a
+ -> Seq Scan on update_test t1
+ -> Sort
+ Sort Key: t2.a
+ -> Seq Scan on update_test t2
+(11 rows)
+
+UPDATE update_test t SET b = 20 WHERE a = 10 ORDER BY t.b + 0;
--
-- Test VALUES in FROM
--
diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql
index a8a028f..0c91f8f 100644
--- a/src/test/regress/sql/update.sql
+++ b/src/test/regress/sql/update.sql
@@ -27,6 +27,32 @@ UPDATE update_test t SET b = t.b + 10 WHERE t.a = 10;
SELECT * FROM update_test;
--
+-- test ORDER BY
+--
+EXPLAIN (COSTS OFF) UPDATE update_test SET b = 10 ORDER BY b;
+
+EXPLAIN (COSTS OFF) UPDATE update_test t SET b = 10 ORDER BY t.b;
+
+EXPLAIN (COSTS OFF) UPDATE update_test SET b = 10 ORDER BY columndoesnotexist;
+
+EXPLAIN (COSTS OFF) UPDATE update_test t SET b = 10 ORDER BY t.b + 0;
+
+EXPLAIN (COSTS OFF) UPDATE update_test t SET b = 20 WHERE a = 10 ORDER BY t.b + 0;
+
+EXPLAIN (COSTS OFF) UPDATE update_test t1 SET b = 20 FROM update_test t2 WHERE t1.a = t2.a ORDER BY t2.b;
+
+UPDATE update_test t SET b = 20 WHERE a = 10 ORDER BY t.b + 0;
+
+--
+-- test LIMIT
+--
+EXPLAIN (COSTS OFF) UPDATE update_test SET b = 10 LIMIT 1;
+
+EXPLAIN (COSTS OFF) UPDATE update_test SET b = 10 ORDER BY b LIMIT 1;
+
+UPDATE update_test SET b = 0 LIMIT 0 RETURNING *;
+
+--
-- Test VALUES in FROM
--