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

Commite2a8804

Browse files
committed
Support EXECUTE USING in plpgsql.
Pavel Stehule, with some improvements by myself.
1 parentd5466e3 commite2a8804

File tree

8 files changed

+350
-60
lines changed

8 files changed

+350
-60
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 57 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.125 2008/03/28 00:21:55 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.126 2008/04/01 03:51:09 tgl Exp $ -->
22

33
<chapter id="plpgsql">
44
<title><application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language</title>
@@ -1005,20 +1005,23 @@ END;
10051005
<command>EXECUTE</command> statement is provided:
10061006

10071007
<synopsis>
1008-
EXECUTE <replaceable class="command">command-string</replaceable> <optional> INTO <optional>STRICT</optional> <replaceable>target</replaceable> </optional>;
1008+
EXECUTE <replaceable class="command">command-string</replaceable> <optional> INTO <optional>STRICT</optional> <replaceable>target</replaceable> </optional> <optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>;
10091009
</synopsis>
10101010

10111011
where <replaceable>command-string</replaceable> is an expression
10121012
yielding a string (of type <type>text</type>) containing the
1013-
command to be executed and <replaceable>target</replaceable> is a
1014-
record variable, row variable, or a comma-separated list of
1015-
simple variables and record/row fields.
1013+
command to be executed. The optional <replaceable>target</replaceable>
1014+
is a record variable, a row variable, or a comma-separated list of
1015+
simple variables and record/row fields, into which the results of
1016+
the command will be stored. The optional <literal>USING</> expressions
1017+
supply values to be inserted into the command.
10161018
</para>
10171019

10181020
<para>
10191021
No substitution of <application>PL/pgSQL</> variables is done on the
10201022
computed command string. Any required variable values must be inserted
1021-
in the command string as it is constructed.
1023+
in the command string as it is constructed; or you can use parameters
1024+
as described below.
10221025
</para>
10231026

10241027
<para>
@@ -1046,6 +1049,51 @@ EXECUTE <replaceable class="command">command-string</replaceable> <optional> INT
10461049
If the <literal>STRICT</> option is given, an error is reported
10471050
unless the query produces exactly one row.
10481051
</para>
1052+
1053+
<para>
1054+
The command string can use parameter values, which are referenced
1055+
in the command as <literal>$1</>, <literal>$2</>, etc.
1056+
These symbols refer to values supplied in the <literal>USING</>
1057+
clause. This method is often preferable to inserting data values
1058+
into the command string as text: it avoids run-time overhead of
1059+
converting the values to text and back, and it is much less prone
1060+
to SQL-injection attacks since there is no need for quoting or escaping.
1061+
An example is:
1062+
<programlisting>
1063+
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
1064+
INTO c
1065+
USING checked_user, checked_date;
1066+
</programlisting>
1067+
1068+
Note that parameter symbols can only be used for data values
1069+
&mdash; if you want to use dynamically determined table or column
1070+
names, you must insert them into the command string textually.
1071+
For example, if the preceding query needed to be done against a
1072+
dynamically selected table, you could do this:
1073+
<programlisting>
1074+
EXECUTE 'SELECT count(*) FROM '
1075+
|| tabname::regclass
1076+
|| ' WHERE inserted_by = $1 AND inserted <= $2'
1077+
INTO c
1078+
USING checked_user, checked_date;
1079+
</programlisting>
1080+
</para>
1081+
1082+
<para>
1083+
An <command>EXECUTE</> with a simple constant command string and some
1084+
<literal>USING</> parameters, as in the first example above, is
1085+
functionally equivalent to just writing the command directly in
1086+
<application>PL/pgSQL</application> and allowing replacement of
1087+
<application>PL/pgSQL</application> variables to happen automatically.
1088+
The important difference is that <command>EXECUTE</> will re-plan
1089+
the command on each execution, generating a plan that is specific
1090+
to the current parameter values; whereas
1091+
<application>PL/pgSQL</application> normally creates a generic plan
1092+
and caches it for re-use. In situations where the best plan depends
1093+
strongly on the parameter values, <command>EXECUTE</> can be
1094+
significantly faster; while when the plan is not sensitive to parameter
1095+
values, re-planning will be a waste.
1096+
</para>
10491097

10501098
<para>
10511099
<command>SELECT INTO</command> is not currently supported within
@@ -1997,7 +2045,7 @@ $$ LANGUAGE plpgsql;
19972045
rows:
19982046
<synopsis>
19992047
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
2000-
FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> LOOP
2048+
FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</replaceable><optional> USING <replaceable>expression</replaceable> <optional>, ...</optional> </optional>LOOP
20012049
<replaceable>statements</replaceable>
20022050
END LOOP <optional> <replaceable>label</replaceable> </optional>;
20032051
</synopsis>
@@ -2006,6 +2054,8 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>;
20062054
on each entry to the <literal>FOR</> loop. This allows the programmer to
20072055
choose the speed of a preplanned query or the flexibility of a dynamic
20082056
query, just as with a plain <command>EXECUTE</command> statement.
2057+
As with <command>EXECUTE</command>, parameter values can be inserted
2058+
into the dynamic command via <literal>USING</>.
20092059
</para>
20102060
</sect2>
20112061

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

Lines changed: 82 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.108 2008/01/0119:46:00 momjian Exp $
12+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.109 2008/04/0103:51:09 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -21,11 +21,15 @@
2121

2222
static PLpgSQL_expr*read_sql_construct(int until,
2323
int until2,
24+
int until3,
2425
constchar *expected,
2526
constchar *sqlstart,
2627
bool isexpression,
2728
bool valid_sql,
2829
int *endtoken);
30+
static PLpgSQL_expr*read_sql_expression2(int until,int until2,
31+
constchar *expected,
32+
int *endtoken);
2933
staticPLpgSQL_expr*read_sql_stmt(constchar *sqlstart);
3034
staticPLpgSQL_type*read_datatype(int tok);
3135
staticPLpgSQL_stmt*make_execsql_stmt(constchar *sqlstart,int lineno);
@@ -200,6 +204,7 @@ staticvoid check_labels(const char *start_label,
200204
%tokenK_THEN
201205
%tokenK_TO
202206
%tokenK_TYPE
207+
%tokenK_USING
203208
%tokenK_WARNING
204209
%tokenK_WHEN
205210
%tokenK_WHILE
@@ -892,8 +897,11 @@ for_control:
892897
{
893898
PLpgSQL_stmt_dynfors*new;
894899
PLpgSQL_expr*expr;
900+
intterm;
895901

896-
expr = plpgsql_read_expression(K_LOOP,"LOOP");
902+
expr = read_sql_expression2(K_LOOP, K_USING,
903+
"LOOP or USING",
904+
&term);
897905

898906
new = palloc0(sizeof(PLpgSQL_stmt_dynfors));
899907
new->cmd_type = PLPGSQL_STMT_DYNFORS;
@@ -921,6 +929,17 @@ for_control:
921929
}
922930
new->query = expr;
923931

932+
if (term == K_USING)
933+
{
934+
do
935+
{
936+
expr = read_sql_expression2(',', K_LOOP,
937+
", or LOOP",
938+
&term);
939+
new->params = lappend(new->params, expr);
940+
}while (term ==',');
941+
}
942+
924943
$$ = (PLpgSQL_stmt *)new;
925944
}
926945
else
@@ -954,6 +973,7 @@ for_control:
954973
*/
955974
expr1 = read_sql_construct(K_DOTDOT,
956975
K_LOOP,
976+
0,
957977
"LOOP",
958978
"SELECT",
959979
true,
@@ -973,17 +993,14 @@ for_control:
973993
check_sql_expr(expr1->query);
974994

975995
/* Read and check the second one*/
976-
expr2 = read_sql_construct(K_LOOP,
977-
K_BY,
978-
"LOOP",
979-
"SELECT",
980-
true,
981-
true,
982-
&tok);
996+
expr2 = read_sql_expression2(K_LOOP, K_BY,
997+
"LOOP",
998+
&tok);
983999

9841000
/* Get the BY clause if any*/
9851001
if (tok == K_BY)
986-
expr_by = plpgsql_read_expression(K_LOOP,"LOOP");
1002+
expr_by = plpgsql_read_expression(K_LOOP,
1003+
"LOOP");
9871004
else
9881005
expr_by =NULL;
9891006

@@ -1217,18 +1234,15 @@ stmt_raise: K_RAISE lno raise_level raise_msg
12171234

12181235
if (tok ==',')
12191236
{
1220-
PLpgSQL_expr *expr;
1221-
int term;
1222-
1223-
for (;;)
1237+
do
12241238
{
1225-
expr = read_sql_construct(',',';',", or ;",
1226-
"SELECT",
1227-
true,true, &term);
1239+
PLpgSQL_expr *expr;
1240+
1241+
expr = read_sql_expression2(',',';',
1242+
", or ;",
1243+
&tok);
12281244
new->params = lappend(new->params, expr);
1229-
if (term ==';')
1230-
break;
1231-
}
1245+
}while (tok ==',');
12321246
}
12331247

12341248
$$ = (PLpgSQL_stmt *)new;
@@ -1307,7 +1321,8 @@ stmt_dynexecute : K_EXECUTE lno
13071321
PLpgSQL_expr *expr;
13081322
int endtoken;
13091323

1310-
expr = read_sql_construct(K_INTO,';',"INTO|;",
1324+
expr = read_sql_construct(K_INTO, K_USING,';',
1325+
"INTO or USING or ;",
13111326
"SELECT",
13121327
true,true, &endtoken);
13131328

@@ -1319,16 +1334,30 @@ stmt_dynexecute : K_EXECUTE lno
13191334
new->strict =false;
13201335
new->rec =NULL;
13211336
new->row =NULL;
1337+
new->params = NIL;
13221338

13231339
/* If we found "INTO", collect the argument*/
13241340
if (endtoken == K_INTO)
13251341
{
13261342
new->into =true;
13271343
read_into_target(&new->rec, &new->row, &new->strict);
1328-
if (yylex() !=';')
1344+
endtoken =yylex();
1345+
if (endtoken !=';' && endtoken != K_USING)
13291346
yyerror("syntax error");
13301347
}
13311348

1349+
/* If we found "USING", collect the argument(s)*/
1350+
if (endtoken == K_USING)
1351+
{
1352+
do
1353+
{
1354+
expr = read_sql_expression2(',',';',
1355+
", or ;",
1356+
&endtoken);
1357+
new->params = lappend(new->params, expr);
1358+
}while (endtoken ==',');
1359+
}
1360+
13321361
$$ = (PLpgSQL_stmt *)new;
13331362
}
13341363
;
@@ -1485,7 +1514,7 @@ stmt_fetch: K_FETCH lno opt_fetch_direction cursor_variable K_INTO
14851514
$$ = (PLpgSQL_stmt *)fetch;
14861515
}
14871516
;
1488-
1517+
14891518
stmt_move:K_MOVElnoopt_fetch_directioncursor_variable';'
14901519
{
14911520
PLpgSQL_stmt_fetch *fetch =$3;
@@ -1730,33 +1759,48 @@ assign_expr_param(int dno, int *params, int *nparams)
17301759
}
17311760

17321761

1762+
/* Convenience routine to read an expression with one possible terminator*/
17331763
PLpgSQL_expr *
17341764
plpgsql_read_expression(int until,constchar *expected)
17351765
{
1736-
returnread_sql_construct(until,0, expected,"SELECT",true,true,NULL);
1766+
returnread_sql_construct(until,0,0, expected,
1767+
"SELECT",true,true,NULL);
17371768
}
17381769

1770+
/* Convenience routine to read an expression with two possible terminators*/
1771+
static PLpgSQL_expr *
1772+
read_sql_expression2(int until,int until2,constchar *expected,
1773+
int *endtoken)
1774+
{
1775+
returnread_sql_construct(until, until2,0, expected,
1776+
"SELECT",true,true, endtoken);
1777+
}
1778+
1779+
/* Convenience routine to read a SQL statement that must end with ';'*/
17391780
static PLpgSQL_expr *
17401781
read_sql_stmt(constchar *sqlstart)
17411782
{
1742-
returnread_sql_construct(';',0,";", sqlstart,false,true,NULL);
1783+
returnread_sql_construct(';',0,0,";",
1784+
sqlstart,false,true,NULL);
17431785
}
17441786

17451787
/*
17461788
* Read a SQL construct and build a PLpgSQL_expr for it.
17471789
*
17481790
* until:token code for expected terminator
17491791
* until2:token code for alternate terminator (pass 0 if none)
1792+
* until3:token code for another alternate terminator (pass 0 if none)
17501793
* expected:text to use in complaining that terminator was not found
17511794
* sqlstart:text to prefix to the accumulated SQL text
17521795
* isexpression: whether to say we're reading an "expression" or a "statement"
17531796
* valid_sql: whether to check the syntax of the expr (prefixed with sqlstart)
17541797
* endtoken:if not NULL, ending token is stored at *endtoken
1755-
*(this is only interesting if until2 isn't zero)
1798+
*(this is only interesting if until2or until3isn't zero)
17561799
*/
17571800
static PLpgSQL_expr *
17581801
read_sql_construct(int until,
17591802
int until2,
1803+
int until3,
17601804
constchar *expected,
17611805
constchar *sqlstart,
17621806
bool isexpression,
@@ -1783,6 +1827,8 @@ read_sql_construct(int until,
17831827
break;
17841828
if (tok == until2 && parenlevel ==0)
17851829
break;
1830+
if (tok == until3 && parenlevel ==0)
1831+
break;
17861832
if (tok =='(' || tok =='[')
17871833
parenlevel++;
17881834
elseif (tok ==')' || tok ==']')
@@ -2066,15 +2112,17 @@ read_fetch_direction(void)
20662112
elseif (pg_strcasecmp(yytext,"absolute") ==0)
20672113
{
20682114
fetch->direction = FETCH_ABSOLUTE;
2069-
fetch->expr =read_sql_construct(K_FROM, K_IN,"FROM or IN",
2070-
"SELECT",true,true,NULL);
2115+
fetch->expr =read_sql_expression2(K_FROM, K_IN,
2116+
"FROM or IN",
2117+
NULL);
20712118
check_FROM =false;
20722119
}
20732120
elseif (pg_strcasecmp(yytext,"relative") ==0)
20742121
{
20752122
fetch->direction = FETCH_RELATIVE;
2076-
fetch->expr =read_sql_construct(K_FROM, K_IN,"FROM or IN",
2077-
"SELECT",true,true,NULL);
2123+
fetch->expr =read_sql_expression2(K_FROM, K_IN,
2124+
"FROM or IN",
2125+
NULL);
20782126
check_FROM =false;
20792127
}
20802128
elseif (pg_strcasecmp(yytext,"forward") ==0)
@@ -2088,8 +2136,9 @@ read_fetch_direction(void)
20882136
elseif (tok != T_SCALAR)
20892137
{
20902138
plpgsql_push_back_token(tok);
2091-
fetch->expr =read_sql_construct(K_FROM, K_IN,"FROM or IN",
2092-
"SELECT",true,true,NULL);
2139+
fetch->expr =read_sql_expression2(K_FROM, K_IN,
2140+
"FROM or IN",
2141+
NULL);
20932142
check_FROM =false;
20942143
}
20952144
else
@@ -2233,7 +2282,7 @@ make_return_query_stmt(int lineno)
22332282
new =palloc0(sizeof(PLpgSQL_stmt_return_query));
22342283
new->cmd_type = PLPGSQL_STMT_RETURN_QUERY;
22352284
new->lineno = lineno;
2236-
new->query =read_sql_construct(';',0,")","",false,true,NULL);
2285+
new->query =read_sql_stmt("");
22372286

22382287
return (PLpgSQL_stmt *)new;
22392288
}

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp