From 8f889b1083f38f4f5b3bd3512008a3f60e939244 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 18 Jun 2014 13:22:25 -0400 Subject: Implement UPDATE tab SET (col1,col2,...) = (SELECT ...), ... This SQL-standard feature allows a sub-SELECT yielding multiple columns (but only one row) to be used to compute the new values of several columns to be updated. While the same results can be had with an independent sub-SELECT per column, such a workaround can require a great deal of duplicated computation. The standard actually says that the source for a multi-column assignment could be any row-valued expression. The implementation used here is tightly tied to our existing sub-SELECT support and can't handle other cases; the Bison grammar would have some issues with them too. However, I don't feel too bad about this since other cases can be converted into sub-SELECTs. For instance, "SET (a,b,c) = row_valued_function(x)" could be written "SET (a,b,c) = (SELECT * FROM row_valued_function(x))". --- doc/src/sgml/ref/update.sgml | 74 +++++++++++++++++++++++++++++++++++--------- doc/src/sgml/rules.sgml | 21 +++++++++++++ 2 files changed, 81 insertions(+), 14 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml index 7f565be3c20..35b0699f086 100644 --- a/doc/src/sgml/ref/update.sgml +++ b/doc/src/sgml/ref/update.sgml @@ -24,7 +24,9 @@ PostgreSQL documentation [ WITH [ RECURSIVE ] with_query [, ...] ] UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ] SET { column_name = { expression | DEFAULT } | - ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...] + ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) | + ( column_name [, ...] ) = ( sub-SELECT ) + } [, ...] [ FROM from_list ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] @@ -146,6 +148,21 @@ UPDATE [ ONLY ] table_name [ * ] [ + + sub-SELECT + + + A SELECT sub-query that produces as many output columns + as are listed in the parenthesized column list preceding it. The + sub-query must yield no more than one row when executed. If it + yields one row, its column values are assigned to the target columns; + if it yields no rows, NULL values are assigned to the target columns. + The sub-query can refer to old values of the current row of the table + being updated. + + + + from_list @@ -324,6 +341,38 @@ UPDATE employees SET sales_count = sales_count + 1 WHERE id = + + Update contact names in an accounts table to match the currently assigned + salesmen: + +UPDATE accounts SET (contact_first_name, contact_last_name) = + (SELECT first_name, last_name FROM salesmen + WHERE salesmen.id = accounts.sales_id); + + A similar result could be accomplished with a join: + +UPDATE accounts SET contact_first_name = first_name, + contact_last_name = last_name + FROM salesmen WHERE salesmen.id = accounts.sales_id; + + However, the second query may give unexpected results + if salesmen.id is not a unique key, whereas + the first query is guaranteed to raise an error if there are multiple + id matches. Also, if there is no match for a particular + accounts.sales_id entry, the first query + will set the corresponding name fields to NULL, whereas the second query + will not update that row at all. + + + + Update statistics in a summary table to match the current data: + +UPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) = + (SELECT sum(x), sum(y), avg(x), avg(y) FROM data d + WHERE d.group_id = s.group_id); + + + Attempt to insert a new stock item along with the quantity of stock. If the item already exists, instead update the stock count of the existing @@ -361,19 +410,6 @@ UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films; to use WITH with UPDATE. - - According to the standard, the column-list syntax should allow a list - of columns to be assigned from a single row-valued expression, such - as a sub-select: - -UPDATE accounts SET (contact_last_name, contact_first_name) = - (SELECT last_name, first_name FROM salesmen - WHERE salesmen.id = accounts.sales_id); - - This is not currently implemented — the source must be a list - of independent expressions. - - Some other database systems offer a FROM option in which the target table is supposed to be listed again within FROM. @@ -381,5 +417,15 @@ UPDATE accounts SET (contact_last_name, contact_first_name) = FROM. Be careful when porting applications that use this extension. + + + According to the standard, the source value for a parenthesized sub-list of + column names can be any row-valued expression yielding the correct number + of columns. PostgreSQL only allows the source + value to be a parenthesized list of expressions (a row constructor) or a + sub-SELECT. An individual column's updated value can be + specified as DEFAULT in the row-constructor case, but not + inside a sub-SELECT. + diff --git a/doc/src/sgml/rules.sgml b/doc/src/sgml/rules.sgml index 8e34fb0bf2b..66b3cc9bf2f 100644 --- a/doc/src/sgml/rules.sgml +++ b/doc/src/sgml/rules.sgml @@ -1164,6 +1164,27 @@ SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10; original one. + + + In many cases, tasks that could be performed by rules + on INSERT/UPDATE/DELETE are better done + with triggers. Triggers are notationally a bit more complicated, but their + semantics are much simpler to understand. Rules tend to have surprising + results when the original query contains volatile functions: volatile + functions may get executed more times than expected in the process of + carrying out the rules. + + + + Also, there are some cases that are not supported by these types of rules at + all, notably including WITH clauses in the original query and + multiple-assignment sub-SELECTs in the SET list + of UPDATE queries. This is because copying these constructs + into a rule query would result in multiple evaluations of the sub-query, + contrary to the express intent of the query's author. + + + How Update Rules Work -- cgit v1.2.3