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

Commit8f889b1

Browse files
committed
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 columnsto be updated. While the same results can be had with an independentsub-SELECT per column, such a workaround can require a great deal ofduplicated computation.The standard actually says that the source for a multi-column assignmentcould be any row-valued expression. The implementation used here istightly tied to our existing sub-SELECT support and can't handle othercases; 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 intosub-SELECTs. For instance, "SET (a,b,c) = row_valued_function(x)" couldbe written "SET (a,b,c) = (SELECT * FROM row_valued_function(x))".
1 parent230ba02 commit8f889b1

File tree

31 files changed

+805
-122
lines changed

31 files changed

+805
-122
lines changed

‎contrib/pg_stat_statements/pg_stat_statements.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2396,6 +2396,7 @@ JumbleExpr(pgssJumbleState *jstate, Node *node)
23962396
SubLink*sublink= (SubLink*)node;
23972397

23982398
APP_JUMB(sublink->subLinkType);
2399+
APP_JUMB(sublink->subLinkId);
23992400
JumbleExpr(jstate, (Node*)sublink->testexpr);
24002401
JumbleQuery(jstate, (Query*)sublink->subselect);
24012402
}

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

Lines changed: 60 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -24,7 +24,9 @@ PostgreSQL documentation
2424
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
2525
UPDATE [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
2626
SET { <replaceable class="PARAMETER">column_name</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } |
27-
( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) } [, ...]
27+
( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) |
28+
( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) = ( <replaceable class="PARAMETER">sub-SELECT</replaceable> )
29+
} [, ...]
2830
[ FROM <replaceable class="PARAMETER">from_list</replaceable> ]
2931
[ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ]
3032
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
@@ -146,6 +148,21 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ * ] [
146148
</listitem>
147149
</varlistentry>
148150

151+
<varlistentry>
152+
<term><replaceable class="PARAMETER">sub-SELECT</replaceable></term>
153+
<listitem>
154+
<para>
155+
A <literal>SELECT</> sub-query that produces as many output columns
156+
as are listed in the parenthesized column list preceding it. The
157+
sub-query must yield no more than one row when executed. If it
158+
yields one row, its column values are assigned to the target columns;
159+
if it yields no rows, NULL values are assigned to the target columns.
160+
The sub-query can refer to old values of the current row of the table
161+
being updated.
162+
</para>
163+
</listitem>
164+
</varlistentry>
165+
149166
<varlistentry>
150167
<term><replaceable class="PARAMETER">from_list</replaceable></term>
151168
<listitem>
@@ -324,6 +341,38 @@ UPDATE employees SET sales_count = sales_count + 1 WHERE id =
324341
</programlisting>
325342
</para>
326343

344+
<para>
345+
Update contact names in an accounts table to match the currently assigned
346+
salesmen:
347+
<programlisting>
348+
UPDATE accounts SET (contact_first_name, contact_last_name) =
349+
(SELECT first_name, last_name FROM salesmen
350+
WHERE salesmen.id = accounts.sales_id);
351+
</programlisting>
352+
A similar result could be accomplished with a join:
353+
<programlisting>
354+
UPDATE accounts SET contact_first_name = first_name,
355+
contact_last_name = last_name
356+
FROM salesmen WHERE salesmen.id = accounts.sales_id;
357+
</programlisting>
358+
However, the second query may give unexpected results
359+
if <structname>salesmen</>.<structfield>id</> is not a unique key, whereas
360+
the first query is guaranteed to raise an error if there are multiple
361+
<structfield>id</> matches. Also, if there is no match for a particular
362+
<structname>accounts</>.<structfield>sales_id</> entry, the first query
363+
will set the corresponding name fields to NULL, whereas the second query
364+
will not update that row at all.
365+
</para>
366+
367+
<para>
368+
Update statistics in a summary table to match the current data:
369+
<programlisting>
370+
UPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) =
371+
(SELECT sum(x), sum(y), avg(x), avg(y) FROM data d
372+
WHERE d.group_id = s.group_id);
373+
</programlisting>
374+
</para>
375+
327376
<para>
328377
Attempt to insert a new stock item along with the quantity of stock. If
329378
the item already exists, instead update the stock count of the existing
@@ -361,25 +410,22 @@ UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;
361410
to use <literal>WITH</> with <command>UPDATE</>.
362411
</para>
363412

364-
<para>
365-
According to the standard, the column-list syntax should allow a list
366-
of columns to be assigned from a single row-valued expression, such
367-
as a sub-select:
368-
<programlisting>
369-
UPDATE accounts SET (contact_last_name, contact_first_name) =
370-
(SELECT last_name, first_name FROM salesmen
371-
WHERE salesmen.id = accounts.sales_id);
372-
</programlisting>
373-
This is not currently implemented &mdash; the source must be a list
374-
of independent expressions.
375-
</para>
376-
377413
<para>
378414
Some other database systems offer a <literal>FROM</> option in which
379415
the target table is supposed to be listed again within <literal>FROM</>.
380416
That is not how <productname>PostgreSQL</productname> interprets
381417
<literal>FROM</>. Be careful when porting applications that use this
382418
extension.
383419
</para>
420+
421+
<para>
422+
According to the standard, the source value for a parenthesized sub-list of
423+
column names can be any row-valued expression yielding the correct number
424+
of columns. <productname>PostgreSQL</productname> only allows the source
425+
value to be a parenthesized list of expressions (a row constructor) or a
426+
sub-<literal>SELECT</>. An individual column's updated value can be
427+
specified as <literal>DEFAULT</> in the row-constructor case, but not
428+
inside a sub-<literal>SELECT</>.
429+
</para>
384430
</refsect1>
385431
</refentry>

‎doc/src/sgml/rules.sgml

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1164,6 +1164,27 @@ SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10;
11641164
original one.
11651165
</para>
11661166

1167+
<caution>
1168+
<para>
1169+
In many cases, tasks that could be performed by rules
1170+
on <command>INSERT</>/<command>UPDATE</>/<command>DELETE</> are better done
1171+
with triggers. Triggers are notationally a bit more complicated, but their
1172+
semantics are much simpler to understand. Rules tend to have surprising
1173+
results when the original query contains volatile functions: volatile
1174+
functions may get executed more times than expected in the process of
1175+
carrying out the rules.
1176+
</para>
1177+
1178+
<para>
1179+
Also, there are some cases that are not supported by these types of rules at
1180+
all, notably including <literal>WITH</> clauses in the original query and
1181+
multiple-assignment sub-<literal>SELECT</>s in the <literal>SET</> list
1182+
of <command>UPDATE</> queries. This is because copying these constructs
1183+
into a rule query would result in multiple evaluations of the sub-query,
1184+
contrary to the express intent of the query's author.
1185+
</para>
1186+
</caution>
1187+
11671188
<sect2>
11681189
<title>How Update Rules Work</title>
11691190

‎src/backend/executor/nodeSubplan.c

Lines changed: 72 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,15 @@
11
/*-------------------------------------------------------------------------
22
*
33
* nodeSubplan.c
4-
* routines to support subselects
4+
* routines to support sub-selects appearing in expressions
5+
*
6+
* This module is concerned with executing SubPlan expression nodes, which
7+
* should not be confused with sub-SELECTs appearing in FROM. SubPlans are
8+
* divided into "initplans", which are those that need only one evaluation per
9+
* query (among other restrictions, this requires that they don't use any
10+
* direct correlation variables from the parent plan level), and "regular"
11+
* subplans, which are re-evaluated every time their result is required.
12+
*
513
*
614
* Portions Copyright (c) 1996-2014, PostgreSQL Global Development Group
715
* Portions Copyright (c) 1994, Regents of the University of California
@@ -54,6 +62,8 @@ static bool slotNoNulls(TupleTableSlot *slot);
5462

5563
/* ----------------------------------------------------------------
5664
*ExecSubPlan
65+
*
66+
* This is the main entry point for execution of a regular SubPlan.
5767
* ----------------------------------------------------------------
5868
*/
5969
staticDatum
@@ -72,7 +82,7 @@ ExecSubPlan(SubPlanState *node,
7282
/* Sanity checks */
7383
if (subplan->subLinkType==CTE_SUBLINK)
7484
elog(ERROR,"CTE subplans should not be executed via ExecSubPlan");
75-
if (subplan->setParam!=NIL)
85+
if (subplan->setParam!=NIL&&subplan->subLinkType!=MULTIEXPR_SUBLINK)
7686
elog(ERROR,"cannot set parent params from subquery");
7787

7888
/* Select appropriate evaluation strategy */
@@ -223,6 +233,32 @@ ExecScanSubPlan(SubPlanState *node,
223233
ListCell*l;
224234
ArrayBuildState*astate=NULL;
225235

236+
/*
237+
* MULTIEXPR subplans, when "executed", just return NULL; but first we
238+
* mark the subplan's output parameters as needing recalculation. (This
239+
* is a bit of a hack: it relies on the subplan appearing later in its
240+
* targetlist than any of the referencing Params, so that all the Params
241+
* have been evaluated before we re-mark them for the next evaluation
242+
* cycle. But in general resjunk tlist items appear after non-resjunk
243+
* ones, so this should be safe.) Unlike ExecReScanSetParamPlan, we do
244+
* *not* set bits in the parent plan node's chgParam, because we don't
245+
* want to cause a rescan of the parent.
246+
*/
247+
if (subLinkType==MULTIEXPR_SUBLINK)
248+
{
249+
EState*estate=node->parent->state;
250+
251+
foreach(l,subplan->setParam)
252+
{
253+
intparamid=lfirst_int(l);
254+
ParamExecData*prm=&(estate->es_param_exec_vals[paramid]);
255+
256+
prm->execPlan=node;
257+
}
258+
*isNull= true;
259+
return (Datum)0;
260+
}
261+
226262
/*
227263
* We are probably in a short-lived expression-evaluation context. Switch
228264
* to the per-query context for manipulating the child plan's chgParam,
@@ -667,6 +703,9 @@ ExecInitSubPlan(SubPlan *subplan, PlanState *parent)
667703
sstate->planstate= (PlanState*)list_nth(estate->es_subplanstates,
668704
subplan->plan_id-1);
669705

706+
/* ... and to its parent's state */
707+
sstate->parent=parent;
708+
670709
/* Initialize subexpressions */
671710
sstate->testexpr=ExecInitExpr((Expr*)subplan->testexpr,parent);
672711
sstate->args= (List*)ExecInitExpr((Expr*)subplan->args,parent);
@@ -690,15 +729,16 @@ ExecInitSubPlan(SubPlan *subplan, PlanState *parent)
690729
sstate->cur_eq_funcs=NULL;
691730

692731
/*
693-
* If this plan is un-correlated or undirect correlated one and want to
694-
* set params for parent plan then mark parameters as needing evaluation.
732+
* If this is an initplan or MULTIEXPR subplan, it has output parameters
733+
* that the parent plan will use, so mark those parameters as needing
734+
* evaluation. We don't actually run the subplan until we first need one
735+
* of its outputs.
695736
*
696737
* A CTE subplan's output parameter is never to be evaluated in the normal
697738
* way, so skip this in that case.
698739
*
699-
* Note that in the case of un-correlated subqueries we don't care about
700-
* setting parent->chgParam here: indices take care about it, for others -
701-
* it doesn't matter...
740+
* Note that we don't set parent->chgParam here: the parent plan hasn't
741+
* been run yet, so no need to force it to re-run.
702742
*/
703743
if (subplan->setParam!=NIL&&subplan->subLinkType!=CTE_SUBLINK)
704744
{
@@ -890,7 +930,7 @@ ExecInitSubPlan(SubPlan *subplan, PlanState *parent)
890930
/* ----------------------------------------------------------------
891931
*ExecSetParamPlan
892932
*
893-
*Executesan InitPlan subplan and sets its output parameters.
933+
*Executesa subplan and sets its output parameters.
894934
*
895935
* This is called from ExecEvalParamExec() when the value of a PARAM_EXEC
896936
* parameter is requested and the param's execPlan field is set (indicating
@@ -908,6 +948,7 @@ ExecSetParamPlan(SubPlanState *node, ExprContext *econtext)
908948
SubLinkTypesubLinkType=subplan->subLinkType;
909949
MemoryContextoldcontext;
910950
TupleTableSlot*slot;
951+
ListCell*pvar;
911952
ListCell*l;
912953
boolfound= false;
913954
ArrayBuildState*astate=NULL;
@@ -923,6 +964,27 @@ ExecSetParamPlan(SubPlanState *node, ExprContext *econtext)
923964
*/
924965
oldcontext=MemoryContextSwitchTo(econtext->ecxt_per_query_memory);
925966

967+
/*
968+
* Set Params of this plan from parent plan correlation values. (Any
969+
* calculation we have to do is done in the parent econtext, since the
970+
* Param values don't need to have per-query lifetime.) Currently, we
971+
* expect only MULTIEXPR_SUBLINK plans to have any correlation values.
972+
*/
973+
Assert(subplan->parParam==NIL||subLinkType==MULTIEXPR_SUBLINK);
974+
Assert(list_length(subplan->parParam)==list_length(node->args));
975+
976+
forboth(l,subplan->parParam,pvar,node->args)
977+
{
978+
intparamid=lfirst_int(l);
979+
ParamExecData*prm=&(econtext->ecxt_param_exec_vals[paramid]);
980+
981+
prm->value=ExecEvalExprSwitchContext((ExprState*)lfirst(pvar),
982+
econtext,
983+
&(prm->isnull),
984+
NULL);
985+
planstate->chgParam=bms_add_member(planstate->chgParam,paramid);
986+
}
987+
926988
/*
927989
* Run the plan. (If it needs to be rescanned, the first ExecProcNode
928990
* call will take care of that.)
@@ -964,6 +1026,7 @@ ExecSetParamPlan(SubPlanState *node, ExprContext *econtext)
9641026

9651027
if (found&&
9661028
(subLinkType==EXPR_SUBLINK||
1029+
subLinkType==MULTIEXPR_SUBLINK||
9671030
subLinkType==ROWCOMPARE_SUBLINK))
9681031
ereport(ERROR,
9691032
(errcode(ERRCODE_CARDINALITY_VIOLATION),
@@ -1035,6 +1098,7 @@ ExecSetParamPlan(SubPlanState *node, ExprContext *econtext)
10351098
}
10361099
else
10371100
{
1101+
/* For other sublink types, set all the output params to NULL */
10381102
foreach(l,subplan->setParam)
10391103
{
10401104
intparamid=lfirst_int(l);

‎src/backend/nodes/copyfuncs.c

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1327,6 +1327,7 @@ _copySubLink(const SubLink *from)
13271327
SubLink*newnode=makeNode(SubLink);
13281328

13291329
COPY_SCALAR_FIELD(subLinkType);
1330+
COPY_SCALAR_FIELD(subLinkId);
13301331
COPY_NODE_FIELD(testexpr);
13311332
COPY_NODE_FIELD(operName);
13321333
COPY_NODE_FIELD(subselect);
@@ -2247,6 +2248,18 @@ _copyResTarget(const ResTarget *from)
22472248
returnnewnode;
22482249
}
22492250

2251+
staticMultiAssignRef*
2252+
_copyMultiAssignRef(constMultiAssignRef*from)
2253+
{
2254+
MultiAssignRef*newnode=makeNode(MultiAssignRef);
2255+
2256+
COPY_NODE_FIELD(source);
2257+
COPY_SCALAR_FIELD(colno);
2258+
COPY_SCALAR_FIELD(ncolumns);
2259+
2260+
returnnewnode;
2261+
}
2262+
22502263
staticTypeName*
22512264
_copyTypeName(constTypeName*from)
22522265
{
@@ -4561,6 +4574,9 @@ copyObject(const void *from)
45614574
caseT_ResTarget:
45624575
retval=_copyResTarget(from);
45634576
break;
4577+
caseT_MultiAssignRef:
4578+
retval=_copyMultiAssignRef(from);
4579+
break;
45644580
caseT_TypeCast:
45654581
retval=_copyTypeCast(from);
45664582
break;

‎src/backend/nodes/equalfuncs.c

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -382,6 +382,7 @@ static bool
382382
_equalSubLink(constSubLink*a,constSubLink*b)
383383
{
384384
COMPARE_SCALAR_FIELD(subLinkType);
385+
COMPARE_SCALAR_FIELD(subLinkId);
385386
COMPARE_NODE_FIELD(testexpr);
386387
COMPARE_NODE_FIELD(operName);
387388
COMPARE_NODE_FIELD(subselect);
@@ -2094,6 +2095,16 @@ _equalResTarget(const ResTarget *a, const ResTarget *b)
20942095
return true;
20952096
}
20962097

2098+
staticbool
2099+
_equalMultiAssignRef(constMultiAssignRef*a,constMultiAssignRef*b)
2100+
{
2101+
COMPARE_NODE_FIELD(source);
2102+
COMPARE_SCALAR_FIELD(colno);
2103+
COMPARE_SCALAR_FIELD(ncolumns);
2104+
2105+
return true;
2106+
}
2107+
20972108
staticbool
20982109
_equalTypeName(constTypeName*a,constTypeName*b)
20992110
{
@@ -3029,6 +3040,9 @@ equal(const void *a, const void *b)
30293040
caseT_ResTarget:
30303041
retval=_equalResTarget(a,b);
30313042
break;
3043+
caseT_MultiAssignRef:
3044+
retval=_equalMultiAssignRef(a,b);
3045+
break;
30323046
caseT_TypeCast:
30333047
retval=_equalTypeCast(a,b);
30343048
break;

‎src/backend/nodes/list.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -385,7 +385,7 @@ list_truncate(List *list, int new_size)
385385
* Locate the n'th cell (counting from 0) of the list. It is an assertion
386386
* failure if there is no such cell.
387387
*/
388-
staticListCell*
388+
ListCell*
389389
list_nth_cell(constList*list,intn)
390390
{
391391
ListCell*match;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp