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

Commit2ace38d

Browse files
committed
Fix WHERE CURRENT OF to work as designed within plpgsql. The argument
can be the name of a plpgsql cursor variable, which formerly was convertedto $N before the core parser saw it, but that's no longer the case.Deal with plain name references to plpgsql variables, and add a regressiontest case that exposes the failure.
1 parent39bd3fd commit2ace38d

File tree

5 files changed

+107
-39
lines changed

5 files changed

+107
-39
lines changed

‎src/backend/executor/execCurrent.c

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Portions Copyright (c) 1996-2009, 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.13 2009/11/04 22:26:05 tgl Exp $
9+
*$PostgreSQL: pgsql/src/backend/executor/execCurrent.c,v 1.14 2009/11/09 02:36:56 tgl Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -20,7 +20,7 @@
2020
#include"utils/portal.h"
2121

2222

23-
staticchar*fetch_param_value(ExprContext*econtext,intparamId);
23+
staticchar*fetch_cursor_param_value(ExprContext*econtext,intparamId);
2424
staticScanState*search_plan_tree(PlanState*node,Oidtable_oid);
2525

2626

@@ -51,7 +51,7 @@ execCurrentOf(CurrentOfExpr *cexpr,
5151
if (cexpr->cursor_name)
5252
cursor_name=cexpr->cursor_name;
5353
else
54-
cursor_name=fetch_param_value(econtext,cexpr->cursor_param);
54+
cursor_name=fetch_cursor_param_value(econtext,cexpr->cursor_param);
5555

5656
/* Fetch table name for possible use in error messages */
5757
table_name=get_rel_name(table_oid);
@@ -203,12 +203,12 @@ execCurrentOf(CurrentOfExpr *cexpr,
203203
}
204204

205205
/*
206-
*fetch_param_value
206+
*fetch_cursor_param_value
207207
*
208208
* Fetch the string value of a param, verifying it is of type REFCURSOR.
209209
*/
210210
staticchar*
211-
fetch_param_value(ExprContext*econtext,intparamId)
211+
fetch_cursor_param_value(ExprContext*econtext,intparamId)
212212
{
213213
ParamListInfoparamInfo=econtext->ecxt_param_list_info;
214214

‎src/backend/parser/gram.y

Lines changed: 1 addition & 9 deletions
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.688 2009/11/05 23:24:23 tgl Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.689 2009/11/09 02:36:56 tgl Exp $
1515
*
1616
* HISTORY
1717
* AUTHORDATEMAJOR EVENT
@@ -7979,14 +7979,6 @@ where_or_current_clause:
79797979
n->cursor_param =0;
79807980
$$ = (Node *) n;
79817981
}
7982-
| WHERE CURRENT_P OF PARAM
7983-
{
7984-
CurrentOfExpr *n = makeNode(CurrentOfExpr);
7985-
/* cvarno is filled in by parse analysis*/
7986-
n->cursor_name =NULL;
7987-
n->cursor_param =$4;
7988-
$$ = (Node *) n;
7989-
}
79907982
|/*EMPTY*/{$$ =NULL; }
79917983
;
79927984

‎src/backend/parser/parse_expr.c

Lines changed: 35 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/parser/parse_expr.c,v 1.247 2009/10/31 01:41:31 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/parser/parse_expr.c,v 1.248 2009/11/09 02:36:56 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1963,32 +1963,42 @@ transformCurrentOfExpr(ParseState *pstate, CurrentOfExpr *cexpr)
19631963
Assert(sublevels_up==0);
19641964

19651965
/*
1966-
*If a parameter is used, it must beof type REFCURSOR. To verify
1967-
*that the parameter hooks think so, build a dummy ParamRef and
1968-
*transform it.
1966+
*Check to see if the cursor name matches a parameterof type REFCURSOR.
1967+
*If so, replace the raw name reference with a parameter reference.
1968+
*(This is a hack for the convenience of plpgsql.)
19691969
*/
1970-
if (cexpr->cursor_name==NULL)
1970+
if (cexpr->cursor_name!=NULL)/* in case already transformed */
19711971
{
1972-
ParamRef*p=makeNode(ParamRef);
1973-
Node*n;
1974-
1975-
p->number=cexpr->cursor_param;
1976-
p->location=-1;
1977-
n=transformParamRef(pstate,p);
1978-
/* Allow the parameter type to be inferred if it's unknown */
1979-
if (exprType(n)==UNKNOWNOID)
1980-
n=coerce_type(pstate,n,UNKNOWNOID,
1981-
REFCURSOROID,-1,
1982-
COERCION_IMPLICIT,COERCE_IMPLICIT_CAST,
1983-
-1);
1984-
if (exprType(n)!=REFCURSOROID)
1985-
ereport(ERROR,
1986-
(errcode(ERRCODE_AMBIGUOUS_PARAMETER),
1987-
errmsg("inconsistent types deduced for parameter $%d",
1988-
cexpr->cursor_param),
1989-
errdetail("%s versus %s",
1990-
format_type_be(exprType(n)),
1991-
format_type_be(REFCURSOROID))));
1972+
ColumnRef*cref=makeNode(ColumnRef);
1973+
Node*node=NULL;
1974+
1975+
/* Build an unqualified ColumnRef with the given name */
1976+
cref->fields=list_make1(makeString(cexpr->cursor_name));
1977+
cref->location=-1;
1978+
1979+
/* See if there is a translation available from a parser hook */
1980+
if (pstate->p_pre_columnref_hook!=NULL)
1981+
node= (*pstate->p_pre_columnref_hook) (pstate,cref);
1982+
if (node==NULL&&pstate->p_post_columnref_hook!=NULL)
1983+
node= (*pstate->p_post_columnref_hook) (pstate,cref,NULL);
1984+
1985+
/*
1986+
* XXX Should we throw an error if we get a translation that isn't
1987+
* a refcursor Param? For now it seems best to silently ignore
1988+
* false matches.
1989+
*/
1990+
if (node!=NULL&&IsA(node,Param))
1991+
{
1992+
Param*p= (Param*)node;
1993+
1994+
if (p->paramkind==PARAM_EXTERN&&
1995+
p->paramtype==REFCURSOROID)
1996+
{
1997+
/* Matches, so convert CURRENT OF to a param reference */
1998+
cexpr->cursor_name=NULL;
1999+
cexpr->cursor_param=p->paramid;
2000+
}
2001+
}
19922002
}
19932003

19942004
return (Node*)cexpr;

‎src/test/regress/expected/plpgsql.out

Lines changed: 46 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3292,6 +3292,52 @@ select * from forc_test;
32923292
1000 | 20
32933293
(10 rows)
32943294

3295+
-- same, with a cursor whose portal name doesn't match variable name
3296+
create or replace function forc01() returns void as $$
3297+
declare
3298+
c refcursor := 'fooled_ya';
3299+
r record;
3300+
begin
3301+
open c for select * from forc_test;
3302+
loop
3303+
fetch c into r;
3304+
exit when not found;
3305+
raise notice '%, %', r.i, r.j;
3306+
update forc_test set i = i * 100, j = r.j * 2 where current of c;
3307+
end loop;
3308+
end;
3309+
$$ language plpgsql;
3310+
select forc01();
3311+
NOTICE: 100, 2
3312+
NOTICE: 200, 4
3313+
NOTICE: 300, 6
3314+
NOTICE: 400, 8
3315+
NOTICE: 500, 10
3316+
NOTICE: 600, 12
3317+
NOTICE: 700, 14
3318+
NOTICE: 800, 16
3319+
NOTICE: 900, 18
3320+
NOTICE: 1000, 20
3321+
forc01
3322+
--------
3323+
3324+
(1 row)
3325+
3326+
select * from forc_test;
3327+
i | j
3328+
--------+----
3329+
10000 | 4
3330+
20000 | 8
3331+
30000 | 12
3332+
40000 | 16
3333+
50000 | 20
3334+
60000 | 24
3335+
70000 | 28
3336+
80000 | 32
3337+
90000 | 36
3338+
100000 | 40
3339+
(10 rows)
3340+
32953341
drop function forc01();
32963342
-- fail because cursor has no query bound to it
32973343
create or replace function forc_bad() returns void as $$

‎src/test/regress/sql/plpgsql.sql

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2689,6 +2689,26 @@ select forc01();
26892689

26902690
select*from forc_test;
26912691

2692+
-- same, with a cursor whose portal name doesn't match variable name
2693+
create or replacefunctionforc01() returns voidas $$
2694+
declare
2695+
c refcursor :='fooled_ya';
2696+
r record;
2697+
begin
2698+
open c forselect*from forc_test;
2699+
loop
2700+
fetch c into r;
2701+
exit when not found;
2702+
raise notice'%, %',r.i,r.j;
2703+
update forc_testset i= i*100, j=r.j*2where current of c;
2704+
end loop;
2705+
end;
2706+
$$ language plpgsql;
2707+
2708+
select forc01();
2709+
2710+
select*from forc_test;
2711+
26922712
dropfunction forc01();
26932713

26942714
-- fail because cursor has no query bound to it

0 commit comments

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