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

Commita9545b3

Browse files
committed
Improve UPDATE/DELETE WHERE CURRENT OF so that they can be used from plpgsql
with a plpgsql-defined cursor. The underlying mechanism for this is that themain SQL engine will now take "WHERE CURRENT OF $n" where $n is a refcursorparameter. Not sure if we should document that fact or consider it animplementation detail. Per discussion with Pavel Stehule.
1 parentbdc71c2 commita9545b3

File tree

13 files changed

+226
-73
lines changed

13 files changed

+226
-73
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 26 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.111 2007/06/1115:08:32 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.112 2007/06/1122:22:40 tgl Exp $ -->
22

33
<chapter id="plpgsql">
44
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -2614,6 +2614,31 @@ MOVE <optional> <replaceable>direction</replaceable> { FROM | IN } </optional> <
26142614
MOVE curs1;
26152615
MOVE LAST FROM curs3;
26162616
MOVE RELATIVE -2 FROM curs4;
2617+
</programlisting>
2618+
</para>
2619+
</sect3>
2620+
2621+
<sect3>
2622+
<title><literal>UPDATE/DELETE WHERE CURRENT OF</></title>
2623+
2624+
<synopsis>
2625+
UPDATE <replaceable>table</replaceable> SET ... WHERE CURRENT OF <replaceable>cursor</replaceable>;
2626+
DELETE FROM <replaceable>table</replaceable> WHERE CURRENT OF <replaceable>cursor</replaceable>;
2627+
</synopsis>
2628+
2629+
<para>
2630+
When a cursor is positioned on a table row, that row can be updated
2631+
or deleted using the cursor to identify the row. Note that this
2632+
only works for simple (non-join, non-grouping) cursor queries.
2633+
For additional information see the
2634+
<xref linkend="sql-declare" endterm="sql-declare-title">
2635+
reference page.
2636+
</para>
2637+
2638+
<para>
2639+
An example:
2640+
<programlisting>
2641+
UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;
26172642
</programlisting>
26182643
</para>
26192644
</sect3>

‎src/backend/executor/execCurrent.c

Lines changed: 63 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -6,41 +6,55 @@
66
* Portions Copyright (c) 1996-2007, PostgreSQL Global Development Group
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
9-
*$PostgreSQL: pgsql/src/backend/executor/execCurrent.c,v 1.1 2007/06/1101:16:22 tgl Exp $
9+
*$PostgreSQL: pgsql/src/backend/executor/execCurrent.c,v 1.2 2007/06/1122:22:40 tgl Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
1313
#include"postgres.h"
1414

15+
#include"catalog/pg_type.h"
1516
#include"executor/executor.h"
17+
#include"utils/builtins.h"
1618
#include"utils/lsyscache.h"
1719
#include"utils/portal.h"
1820

1921

22+
staticchar*fetch_param_value(ExprContext*econtext,intparamId);
2023
staticScanState*search_plan_tree(PlanState*node,Oidtable_oid);
2124

2225

2326
/*
2427
* execCurrentOf
2528
*
26-
* Giventhe name of a cursor and the OID of a table, determine which row
27-
* of the table is currently being scanned by the cursor, and return its
28-
* TID into *current_tid.
29+
* Givena CURRENT OF expression and the OID of a table, determine which row
30+
* of the table is currently being scanned by the cursor named by CURRENT OF,
31+
*and return the row'sTID into *current_tid.
2932
*
3033
* Returns TRUE if a row was identified. Returns FALSE if the cursor is valid
3134
* for the table but is not currently scanning a row of the table (this is a
3235
* legal situation in inheritance cases). Raises error if cursor is not a
3336
* valid updatable scan of the specified table.
3437
*/
3538
bool
36-
execCurrentOf(char*cursor_name,Oidtable_oid,
39+
execCurrentOf(CurrentOfExpr*cexpr,
40+
ExprContext*econtext,
41+
Oidtable_oid,
3742
ItemPointercurrent_tid)
3843
{
44+
char*cursor_name;
3945
char*table_name;
4046
Portalportal;
4147
QueryDesc*queryDesc;
4248
ScanState*scanstate;
43-
HeapTupletup;
49+
boollisnull;
50+
Oidtuple_tableoid;
51+
ItemPointertuple_tid;
52+
53+
/* Get the cursor name --- may have to look up a parameter reference */
54+
if (cexpr->cursor_name)
55+
cursor_name=cexpr->cursor_name;
56+
else
57+
cursor_name=fetch_param_value(econtext,cexpr->cursor_param);
4458

4559
/* Fetch table name for possible use in error messages */
4660
table_name=get_rel_name(table_oid);
@@ -100,16 +114,54 @@ execCurrentOf(char *cursor_name, Oid table_oid,
100114
if (TupIsNull(scanstate->ss_ScanTupleSlot))
101115
return false;
102116

103-
tup=scanstate->ss_ScanTupleSlot->tts_tuple;
104-
if (tup==NULL)
105-
elog(ERROR,"CURRENT OF applied to non-materialized tuple");
106-
Assert(tup->t_tableOid==table_oid);
117+
/* Use slot_getattr to catch any possible mistakes */
118+
tuple_tableoid=DatumGetObjectId(slot_getattr(scanstate->ss_ScanTupleSlot,
119+
TableOidAttributeNumber,
120+
&lisnull));
121+
Assert(!lisnull);
122+
tuple_tid= (ItemPointer)
123+
DatumGetPointer(slot_getattr(scanstate->ss_ScanTupleSlot,
124+
SelfItemPointerAttributeNumber,
125+
&lisnull));
126+
Assert(!lisnull);
127+
128+
Assert(tuple_tableoid==table_oid);
107129

108-
*current_tid=tup->t_self;
130+
*current_tid=*tuple_tid;
109131

110132
return true;
111133
}
112134

135+
/*
136+
* fetch_param_value
137+
*
138+
* Fetch the string value of a param, verifying it is of type REFCURSOR.
139+
*/
140+
staticchar*
141+
fetch_param_value(ExprContext*econtext,intparamId)
142+
{
143+
ParamListInfoparamInfo=econtext->ecxt_param_list_info;
144+
145+
if (paramInfo&&
146+
paramId>0&&paramId <=paramInfo->numParams)
147+
{
148+
ParamExternData*prm=&paramInfo->params[paramId-1];
149+
150+
if (OidIsValid(prm->ptype)&& !prm->isnull)
151+
{
152+
Assert(prm->ptype==REFCURSOROID);
153+
/* We know that refcursor uses text's I/O routines */
154+
returnDatumGetCString(DirectFunctionCall1(textout,
155+
prm->value));
156+
}
157+
}
158+
159+
ereport(ERROR,
160+
(errcode(ERRCODE_UNDEFINED_OBJECT),
161+
errmsg("no value found for parameter %d",paramId)));
162+
returnNULL;
163+
}
164+
113165
/*
114166
* search_plan_tree
115167
*

‎src/backend/executor/execQual.c

Lines changed: 18 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/execQual.c,v 1.219 2007/06/1101:16:22 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/execQual.c,v 1.220 2007/06/1122:22:40 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -3632,8 +3632,10 @@ ExecEvalCurrentOfExpr(ExprState *exprstate, ExprContext *econtext,
36323632
bool*isNull,ExprDoneCond*isDone)
36333633
{
36343634
CurrentOfExpr*cexpr= (CurrentOfExpr*)exprstate->expr;
3635-
boolresult;
3636-
HeapTupletup;
3635+
boolresult;
3636+
boollisnull;
3637+
Oidtableoid;
3638+
ItemPointertuple_tid;
36373639
ItemPointerDatacursor_tid;
36383640

36393641
if (isDone)
@@ -3643,12 +3645,19 @@ ExecEvalCurrentOfExpr(ExprState *exprstate, ExprContext *econtext,
36433645
Assert(cexpr->cvarno!=INNER);
36443646
Assert(cexpr->cvarno!=OUTER);
36453647
Assert(!TupIsNull(econtext->ecxt_scantuple));
3646-
tup=econtext->ecxt_scantuple->tts_tuple;
3647-
if (tup==NULL)
3648-
elog(ERROR,"CURRENT OF applied to non-materialized tuple");
3649-
3650-
if (execCurrentOf(cexpr->cursor_name,tup->t_tableOid,&cursor_tid))
3651-
result=ItemPointerEquals(&cursor_tid,&(tup->t_self));
3648+
/* Use slot_getattr to catch any possible mistakes */
3649+
tableoid=DatumGetObjectId(slot_getattr(econtext->ecxt_scantuple,
3650+
TableOidAttributeNumber,
3651+
&lisnull));
3652+
Assert(!lisnull);
3653+
tuple_tid= (ItemPointer)
3654+
DatumGetPointer(slot_getattr(econtext->ecxt_scantuple,
3655+
SelfItemPointerAttributeNumber,
3656+
&lisnull));
3657+
Assert(!lisnull);
3658+
3659+
if (execCurrentOf(cexpr,econtext,tableoid,&cursor_tid))
3660+
result=ItemPointerEquals(&cursor_tid,tuple_tid);
36523661
else
36533662
result= false;
36543663

‎src/backend/executor/nodeTidscan.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/nodeTidscan.c,v 1.54 2007/06/1101:16:22 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/nodeTidscan.c,v 1.55 2007/06/1122:22:40 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -153,7 +153,7 @@ TidListCreate(TidScanState *tidstate)
153153
CurrentOfExpr*cexpr= (CurrentOfExpr*)expr;
154154
ItemPointerDatacursor_tid;
155155

156-
if (execCurrentOf(cexpr->cursor_name,
156+
if (execCurrentOf(cexpr,econtext,
157157
RelationGetRelid(tidstate->ss.ss_currentRelation),
158158
&cursor_tid))
159159
{

‎src/backend/nodes/copyfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
* Portions Copyright (c) 1994, Regents of the University of California
1616
*
1717
* IDENTIFICATION
18-
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.378 2007/06/1101:16:22 tgl Exp $
18+
* $PostgreSQL: pgsql/src/backend/nodes/copyfuncs.c,v 1.379 2007/06/1122:22:40 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -1309,6 +1309,7 @@ _copyCurrentOfExpr(CurrentOfExpr *from)
13091309

13101310
COPY_SCALAR_FIELD(cvarno);
13111311
COPY_STRING_FIELD(cursor_name);
1312+
COPY_SCALAR_FIELD(cursor_param);
13121313

13131314
returnnewnode;
13141315
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@
1818
* Portions Copyright (c) 1994, Regents of the University of California
1919
*
2020
* IDENTIFICATION
21-
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.309 2007/06/1101:16:22 tgl Exp $
21+
* $PostgreSQL: pgsql/src/backend/nodes/equalfuncs.c,v 1.310 2007/06/1122:22:40 tgl Exp $
2222
*
2323
*-------------------------------------------------------------------------
2424
*/
@@ -603,6 +603,7 @@ _equalCurrentOfExpr(CurrentOfExpr *a, CurrentOfExpr *b)
603603
{
604604
COMPARE_SCALAR_FIELD(cvarno);
605605
COMPARE_STRING_FIELD(cursor_name);
606+
COMPARE_SCALAR_FIELD(cursor_param);
606607

607608
return true;
608609
}

‎src/backend/nodes/outfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.310 2007/06/1101:16:22 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/nodes/outfuncs.c,v 1.311 2007/06/1122:22:40 tgl Exp $
1212
*
1313
* NOTES
1414
* Every node type that can appear in stored rules' parsetrees *must*
@@ -1065,6 +1065,7 @@ _outCurrentOfExpr(StringInfo str, CurrentOfExpr *node)
10651065

10661066
WRITE_UINT_FIELD(cvarno);
10671067
WRITE_STRING_FIELD(cursor_name);
1068+
WRITE_INT_FIELD(cursor_param);
10681069
}
10691070

10701071
staticvoid

‎src/backend/nodes/readfuncs.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.208 2007/06/1101:16:22 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/nodes/readfuncs.c,v 1.209 2007/06/1122:22:41 tgl Exp $
1212
*
1313
* NOTES
1414
* Path and Plan nodes do not have any readfuncs support, because we
@@ -883,6 +883,7 @@ _readCurrentOfExpr(void)
883883

884884
READ_UINT_FIELD(cvarno);
885885
READ_STRING_FIELD(cursor_name);
886+
READ_INT_FIELD(cursor_param);
886887

887888
READ_DONE();
888889
}

‎src/backend/parser/gram.y

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.592 2007/06/1101:16:25 tgl Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.593 2007/06/1122:22:41 tgl Exp $
1515
*
1616
* HISTORY
1717
* AUTHORDATEMAJOR EVENT
@@ -6568,7 +6568,17 @@ where_or_current_clause:
65686568
| WHERE CURRENT_P OF name
65696569
{
65706570
CurrentOfExpr *n = makeNode(CurrentOfExpr);
6571+
/* cvarno is filled in by parse analysis*/
65716572
n->cursor_name =$4;
6573+
n->cursor_param =0;
6574+
$$ = (Node *) n;
6575+
}
6576+
| WHERE CURRENT_P OF PARAM
6577+
{
6578+
CurrentOfExpr *n = makeNode(CurrentOfExpr);
6579+
/* cvarno is filled in by parse analysis*/
6580+
n->cursor_name =NULL;
6581+
n->cursor_param =$4;
65726582
$$ = (Node *) n;
65736583
}
65746584
|/*EMPTY*/{$$ =NULL; }

0 commit comments

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