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

Commit309cd7c

Browse files
committed
Add "USING expressions" option to plpgsql's OPEN cursor FOR EXECUTE.
This is the last EXECUTE-like plpgsql statement that was missingthe capability of inserting parameter values via USING.Pavel Stehule, reviewed by Itagaki Takahiro
1 parent8ab27af commit309cd7c

File tree

7 files changed

+131
-61
lines changed

7 files changed

+131
-61
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 13 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.149 2009/12/28 19:11:51 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.150 2010/01/19 01:35:30 tgl Exp $ -->
22

33
<chapter id="plpgsql">
44
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -1016,7 +1016,7 @@ END;
10161016
<command>EXECUTE</command> statement is provided:
10171017

10181018
<synopsis>
1019-
EXECUTE <replaceable class="command">command-string</replaceable> <optional> INTO <optional>STRICT</optional> <replaceable>target</replaceable> </optional> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;
1019+
EXECUTE <replaceable class="command">command-string</replaceable> <optional> INTO <optional>STRICT</optional> <replaceable>target</replaceable> </optional> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;
10201020
</synopsis>
10211021

10221022
where <replaceable>command-string</replaceable> is an expression
@@ -1500,7 +1500,7 @@ RETURN <replaceable>expression</replaceable>;
15001500
<synopsis>
15011501
RETURN NEXT <replaceable>expression</replaceable>;
15021502
RETURN QUERY <replaceable>query</replaceable>;
1503-
RETURN QUERY EXECUTE <replaceable class="command">command-string</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;
1503+
RETURN QUERY EXECUTE <replaceable class="command">command-string</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;
15041504
</synopsis>
15051505

15061506
<para>
@@ -2190,7 +2190,7 @@ $$ LANGUAGE plpgsql;
21902190
rows:
21912191
<synopsis>
21922192
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
2193-
FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional> LOOP
2193+
FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional> LOOP
21942194
<replaceable>statements</replaceable>
21952195
END LOOP <optional> <replaceable>label</replaceable> </optional>;
21962196
</synopsis>
@@ -2495,7 +2495,7 @@ OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
24952495
<title><command>OPEN FOR EXECUTE</command></title>
24962496

24972497
<synopsis>
2498-
OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR EXECUTE <replaceable class="command">query_string</replaceable>;
2498+
OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR EXECUTE <replaceable class="command">query_string</replaceable> <optional> USING <replaceable>expression</replaceable> <optional>, ... </optional> </optional>;
24992499
</synopsis>
25002500

25012501
<para>
@@ -2507,7 +2507,8 @@ OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </opt
25072507
command. As usual, this gives flexibility so the query plan can vary
25082508
from one run to the next (see <xref linkend="plpgsql-plan-caching">),
25092509
and it also means that variable substitution is not done on the
2510-
command string.
2510+
command string. As with <command>EXECUTE</command>, parameter values
2511+
can be inserted into the dynamic command via <literal>USING</>.
25112512
The <literal>SCROLL</> and
25122513
<literal>NO SCROLL</> options have the same meanings as for a bound
25132514
cursor.
@@ -2516,8 +2517,12 @@ OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </opt
25162517
<para>
25172518
An example:
25182519
<programlisting>
2519-
OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
2520+
OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident(tabname) ' WHERE col1 = $1' USING keyvalue;
25202521
</programlisting>
2522+
In this example, the table name is inserted into the query textually,
2523+
so use of <function>quote_ident()</> is recommended to guard against
2524+
SQL injection. The comparison value for <literal>col1</> is inserted
2525+
via a <literal>USING</> parameter, so it needs no quoting.
25212526
</para>
25222527
</sect3>
25232528

@@ -2893,7 +2898,7 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>;
28932898
raise errors.
28942899

28952900
<synopsis>
2896-
RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ...</optional></optional> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;
2901+
RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ...</optional></optional> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;
28972902
RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> <replaceable class="parameter">condition_name</> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;
28982903
RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> SQLSTATE '<replaceable class="parameter">sqlstate</>' <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>;
28992904
RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional>;

‎src/pl/plpgsql/src/gram.y

Lines changed: 22 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.139 2010/01/10 17:56:50 tgl Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.140 2010/01/19 01:35:30 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1704,7 +1704,27 @@ stmt_open: K_OPEN cursor_variable
17041704
tok =yylex();
17051705
if (tok == K_EXECUTE)
17061706
{
1707-
new->dynquery =read_sql_stmt("SELECT");
1707+
intendtoken;
1708+
1709+
new->dynquery =
1710+
read_sql_expression2(K_USING,';',
1711+
"USING or ;",
1712+
&endtoken);
1713+
1714+
/* If we found "USING", collect argument(s)*/
1715+
if (endtoken == K_USING)
1716+
{
1717+
PLpgSQL_expr *expr;
1718+
1719+
do
1720+
{
1721+
expr =read_sql_expression2(',',';',
1722+
", or ;",
1723+
&endtoken);
1724+
new->params =lappend(new->params,
1725+
expr);
1726+
}while (endtoken ==',');
1727+
}
17081728
}
17091729
else
17101730
{

‎src/pl/plpgsql/src/pl_exec.c

Lines changed: 22 additions & 48 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.253 2010/01/02 16:58:13 momjian Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.254 2010/01/19 01:35:31 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -200,7 +200,8 @@ static PreparedParamsData *exec_eval_using_params(PLpgSQL_execstate *estate,
200200
List*params);
201201
staticvoidfree_params_data(PreparedParamsData*ppd);
202202
staticPortalexec_dynquery_with_params(PLpgSQL_execstate*estate,
203-
PLpgSQL_expr*query,List*params);
203+
PLpgSQL_expr*dynquery,List*params,
204+
constchar*portalname,intcursorOptions);
204205

205206

206207
/* ----------
@@ -2337,7 +2338,7 @@ exec_stmt_return_query(PLpgSQL_execstate *estate,
23372338
/* RETURN QUERY EXECUTE */
23382339
Assert(stmt->dynquery!=NULL);
23392340
portal=exec_dynquery_with_params(estate,stmt->dynquery,
2340-
stmt->params);
2341+
stmt->params,NULL,0);
23412342
}
23422343

23432344
tupmap=convert_tuples_by_position(portal->tupDesc,
@@ -3133,7 +3134,8 @@ exec_stmt_dynfors(PLpgSQL_execstate *estate, PLpgSQL_stmt_dynfors *stmt)
31333134
Portalportal;
31343135
intrc;
31353136

3136-
portal=exec_dynquery_with_params(estate,stmt->query,stmt->params);
3137+
portal=exec_dynquery_with_params(estate,stmt->query,stmt->params,
3138+
NULL,0);
31373139

31383140
/*
31393141
* Execute the loop
@@ -3161,7 +3163,6 @@ exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt)
31613163
PLpgSQL_expr*query;
31623164
Portalportal;
31633165
ParamListInfoparamLI;
3164-
boolisnull;
31653166

31663167
/* ----------
31673168
* Get the cursor variable and if it has an assigned name, check
@@ -3201,43 +3202,11 @@ exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt)
32013202
* This is an OPEN refcursor FOR EXECUTE ...
32023203
* ----------
32033204
*/
3204-
DatumqueryD;
3205-
Oidrestype;
3206-
char*querystr;
3207-
SPIPlanPtrcurplan;
3208-
3209-
/* ----------
3210-
* We evaluate the string expression after the
3211-
* EXECUTE keyword. It's result is the querystring we have
3212-
* to execute.
3213-
* ----------
3214-
*/
3215-
queryD=exec_eval_expr(estate,stmt->dynquery,&isnull,&restype);
3216-
if (isnull)
3217-
ereport(ERROR,
3218-
(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
3219-
errmsg("query string argument of EXECUTE is null")));
3220-
3221-
/* Get the C-String representation */
3222-
querystr=convert_value_to_string(queryD,restype);
3223-
3224-
exec_eval_cleanup(estate);
3225-
3226-
/* ----------
3227-
* Now we prepare a query plan for it and open a cursor
3228-
* ----------
3229-
*/
3230-
curplan=SPI_prepare_cursor(querystr,0,NULL,stmt->cursor_options);
3231-
if (curplan==NULL)
3232-
elog(ERROR,"SPI_prepare_cursor failed for \"%s\": %s",
3233-
querystr,SPI_result_code_string(SPI_result));
3234-
portal=SPI_cursor_open(curname,curplan,NULL,NULL,
3235-
estate->readonly_func);
3236-
if (portal==NULL)
3237-
elog(ERROR,"could not open cursor for query \"%s\": %s",
3238-
querystr,SPI_result_code_string(SPI_result));
3239-
pfree(querystr);
3240-
SPI_freeplan(curplan);
3205+
portal=exec_dynquery_with_params(estate,
3206+
stmt->dynquery,
3207+
stmt->params,
3208+
curname,
3209+
stmt->cursor_options);
32413210

32423211
/*
32433212
* If cursor variable was NULL, store the generated portal name in it
@@ -5530,8 +5499,11 @@ free_params_data(PreparedParamsData *ppd)
55305499
* Open portal for dynamic query
55315500
*/
55325501
staticPortal
5533-
exec_dynquery_with_params(PLpgSQL_execstate*estate,PLpgSQL_expr*dynquery,
5534-
List*params)
5502+
exec_dynquery_with_params(PLpgSQL_execstate*estate,
5503+
PLpgSQL_expr*dynquery,
5504+
List*params,
5505+
constchar*portalname,
5506+
intcursorOptions)
55355507
{
55365508
Portalportal;
55375509
Datumquery;
@@ -5564,20 +5536,22 @@ exec_dynquery_with_params(PLpgSQL_execstate *estate, PLpgSQL_expr *dynquery,
55645536
PreparedParamsData*ppd;
55655537

55665538
ppd=exec_eval_using_params(estate,params);
5567-
portal=SPI_cursor_open_with_args(NULL,
5539+
portal=SPI_cursor_open_with_args(portalname,
55685540
querystr,
55695541
ppd->nargs,ppd->types,
55705542
ppd->values,ppd->nulls,
5571-
estate->readonly_func,0);
5543+
estate->readonly_func,
5544+
cursorOptions);
55725545
free_params_data(ppd);
55735546
}
55745547
else
55755548
{
5576-
portal=SPI_cursor_open_with_args(NULL,
5549+
portal=SPI_cursor_open_with_args(portalname,
55775550
querystr,
55785551
0,NULL,
55795552
NULL,NULL,
5580-
estate->readonly_func,0);
5553+
estate->readonly_func,
5554+
cursorOptions);
55815555
}
55825556

55835557
if (portal==NULL)

‎src/pl/plpgsql/src/pl_funcs.c

Lines changed: 21 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.87 2010/01/02 16:58:13 momjian Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.88 2010/01/19 01:35:31 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -619,9 +619,28 @@ dump_open(PLpgSQL_stmt_open *stmt)
619619
printf(" execute = '");
620620
dump_expr(stmt->dynquery);
621621
printf("'\n");
622+
623+
if (stmt->params!=NIL)
624+
{
625+
ListCell*lc;
626+
inti;
627+
628+
dump_indent+=2;
629+
dump_ind();
630+
printf(" USING\n");
631+
dump_indent+=2;
632+
i=1;
633+
foreach(lc,stmt->params)
634+
{
635+
dump_ind();
636+
printf(" parameter $%d: ",i++);
637+
dump_expr((PLpgSQL_expr*)lfirst(lc));
638+
printf("\n");
639+
}
640+
dump_indent-=4;
641+
}
622642
}
623643
dump_indent-=2;
624-
625644
}
626645

627646
staticvoid

‎src/pl/plpgsql/src/plpgsql.h

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/pl/plpgsql/src/plpgsql.h,v 1.128 2010/01/10 17:15:18 tgl Exp $
11+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.129 2010/01/19 01:35:31 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -503,6 +503,7 @@ typedef struct
503503
PLpgSQL_expr*argquery;
504504
PLpgSQL_expr*query;
505505
PLpgSQL_expr*dynquery;
506+
List*params;/* USING expressions */
506507
}PLpgSQL_stmt_open;
507508

508509

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

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3189,6 +3189,35 @@ NOTICE: 6
31893189
26
31903190
(1 row)
31913191

3192+
drop function exc_using(int, text);
3193+
create or replace function exc_using(int) returns void as $$
3194+
declare
3195+
c refcursor;
3196+
i int;
3197+
begin
3198+
open c for execute 'select * from generate_series(1,$1)' using $1+1;
3199+
loop
3200+
fetch c into i;
3201+
exit when not found;
3202+
raise notice '%', i;
3203+
end loop;
3204+
close c;
3205+
return;
3206+
end;
3207+
$$ language plpgsql;
3208+
select exc_using(5);
3209+
NOTICE: 1
3210+
NOTICE: 2
3211+
NOTICE: 3
3212+
NOTICE: 4
3213+
NOTICE: 5
3214+
NOTICE: 6
3215+
exc_using
3216+
-----------
3217+
3218+
(1 row)
3219+
3220+
drop function exc_using(int);
31923221
-- test FOR-over-cursor
31933222
create or replace function forc01() returns void as $$
31943223
declare

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

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2629,6 +2629,28 @@ $$ language plpgsql;
26292629

26302630
select exc_using(5,'foobar');
26312631

2632+
dropfunction exc_using(int,text);
2633+
2634+
create or replacefunctionexc_using(int) returns voidas $$
2635+
declare
2636+
c refcursor;
2637+
iint;
2638+
begin
2639+
open c for execute'select * from generate_series(1,$1)' using $1+1;
2640+
loop
2641+
fetch c into i;
2642+
exit when not found;
2643+
raise notice'%', i;
2644+
end loop;
2645+
close c;
2646+
return;
2647+
end;
2648+
$$ language plpgsql;
2649+
2650+
select exc_using(5);
2651+
2652+
dropfunction exc_using(int);
2653+
26322654
-- test FOR-over-cursor
26332655

26342656
create or replacefunctionforc01() returns voidas $$

0 commit comments

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