Hello,
this small patch add missing USING clause to OPEN FOR EXECUTE statement
+ cleaning part of exec_stmt_open function
see http://archives.postgresql.org/pgsql-hackers/2009-11/msg00713.php
Regards
Pavel Stehule
*** ./doc/src/sgml/plpgsql.sgml.orig 2009-11-13 23:43:39.000000000 +0100
--- ./doc/src/sgml/plpgsql.sgml 2009-11-17 20:30:10.656208300 +0100
***************
*** 2488,2494 ****
<title><command>OPEN FOR EXECUTE</command></title>
<synopsis>
! OPEN <replaceable>unbound_cursorvar</replaceable> <optional> <optional> NO </optional> SCROLL </optional> FOR EXECUTE <replaceable class="command">query_string</replaceable>;
</synopsis>
<para>
--- 2488,2494 ----
<title><command>OPEN FOR EXECUTE</command></title>
<synopsis>
! 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>;
</synopsis>
<para>
***************
*** 2500,2506 ****
command. As usual, this gives flexibility so the query plan can vary
from one run to the next (see <xref linkend="plpgsql-plan-caching">),
and it also means that variable substitution is not done on the
! command string.
The <literal>SCROLL</> and
<literal>NO SCROLL</> options have the same meanings as for a bound
cursor.
--- 2500,2507 ----
command. As usual, this gives flexibility so the query plan can vary
from one run to the next (see <xref linkend="plpgsql-plan-caching">),
and it also means that variable substitution is not done on the
! command string. As with <command>EXECUTE</command>, parameter values
! can be inserted into the dynamic command via <literal>USING</>.
The <literal>SCROLL</> and
<literal>NO SCROLL</> options have the same meanings as for a bound
cursor.
***************
*** 2509,2515 ****
<para>
An example:
<programlisting>
! OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
</programlisting>
</para>
</sect3>
--- 2510,2516 ----
<para>
An example:
<programlisting>
! OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1) ' WHERE col1 = $1' USING var1;
</programlisting>
</para>
</sect3>
*** ./src/pl/plpgsql/src/gram.y.orig 2009-11-13 23:43:40.000000000 +0100
--- ./src/pl/plpgsql/src/gram.y 2009-11-17 14:37:33.927208178 +0100
***************
*** 1686,1692 ****
tok = yylex();
if (tok == K_EXECUTE)
{
! new->dynquery = read_sql_stmt("SELECT ");
}
else
{
--- 1686,1712 ----
tok = yylex();
if (tok == K_EXECUTE)
{
! int endtoken;
!
! new->dynquery = read_sql_construct(K_USING, ';', 0,
! "USING or ;",
! "SELECT ",
! true, true,
! NULL, &endtoken);
!
! /* If we found "USING", collect the argument(s) */
! if (endtoken == K_USING)
! {
! PLpgSQL_expr *expr;
!
! do
! {
! expr = read_sql_expression2(',', ';',
! ", or ;",
! &endtoken);
! new->params = lappend(new->params, expr);
! } while (endtoken == ',');
! }
}
else
{
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2009-11-09 01:26:55.000000000 +0100
--- ./src/pl/plpgsql/src/pl_exec.c 2009-11-17 19:48:47.209207349 +0100
***************
*** 199,206 ****
static PreparedParamsData *exec_eval_using_params(PLpgSQL_execstate *estate,
List *params);
static void free_params_data(PreparedParamsData *ppd);
! static Portal exec_dynquery_with_params(PLpgSQL_execstate *estate,
! PLpgSQL_expr *query, List *params);
/* ----------
--- 199,206 ----
static PreparedParamsData *exec_eval_using_params(PLpgSQL_execstate *estate,
List *params);
static void free_params_data(PreparedParamsData *ppd);
! static Portal exec_dynquery_with_params(PLpgSQL_execstate *estate, char *posrtalname,
! PLpgSQL_expr *query, List *params, int cursorOption);
/* ----------
***************
*** 2343,2350 ****
{
/* RETURN QUERY EXECUTE */
Assert(stmt->dynquery != NULL);
! portal = exec_dynquery_with_params(estate, stmt->dynquery,
! stmt->params);
}
tupmap = convert_tuples_by_position(portal->tupDesc,
--- 2343,2350 ----
{
/* RETURN QUERY EXECUTE */
Assert(stmt->dynquery != NULL);
! portal = exec_dynquery_with_params(estate, NULL, stmt->dynquery,
! stmt->params, 0);
}
tupmap = convert_tuples_by_position(portal->tupDesc,
***************
*** 3123,3129 ****
Portal portal;
int rc;
! portal = exec_dynquery_with_params(estate, stmt->query, stmt->params);
/*
* Execute the loop
--- 3123,3129 ----
Portal portal;
int rc;
! portal = exec_dynquery_with_params(estate, NULL, stmt->query, stmt->params, 0);
/*
* Execute the loop
***************
*** 3191,3234 ****
* This is an OPEN refcursor FOR EXECUTE ...
* ----------
*/
! Datum queryD;
! Oid restype;
! char *querystr;
! SPIPlanPtr curplan;
!
! /* ----------
! * We evaluate the string expression after the
! * EXECUTE keyword. It's result is the querystring we have
! * to execute.
! * ----------
! */
! queryD = exec_eval_expr(estate, stmt->dynquery, &isnull, &restype);
! if (isnull)
! ereport(ERROR,
! (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
! errmsg("query string argument of EXECUTE is null")));
!
! /* Get the C-String representation */
! querystr = convert_value_to_string(queryD, restype);
!
! exec_eval_cleanup(estate);
!
! /* ----------
! * Now we prepare a query plan for it and open a cursor
! * ----------
! */
! curplan = SPI_prepare_cursor(querystr, 0, NULL, stmt->cursor_options);
! if (curplan == NULL)
! elog(ERROR, "SPI_prepare_cursor failed for \"%s\": %s",
! querystr, SPI_result_code_string(SPI_result));
! portal = SPI_cursor_open(curname, curplan, NULL, NULL,
! estate->readonly_func);
! if (portal == NULL)
! elog(ERROR, "could not open cursor for query \"%s\": %s",
! querystr, SPI_result_code_string(SPI_result));
! pfree(querystr);
! SPI_freeplan(curplan);
!
/*
* If cursor variable was NULL, store the generated portal name in it
*/
--- 3191,3201 ----
* This is an OPEN refcursor FOR EXECUTE ...
* ----------
*/
! portal = exec_dynquery_with_params(estate, curname,
! stmt->dynquery,
! stmt->params,
! stmt->cursor_options);
!
/*
* If cursor variable was NULL, store the generated portal name in it
*/
***************
*** 5520,5527 ****
* Open portal for dynamic query
*/
static Portal
! exec_dynquery_with_params(PLpgSQL_execstate *estate, PLpgSQL_expr *dynquery,
! List *params)
{
Portal portal;
Datum query;
--- 5487,5496 ----
* Open portal for dynamic query
*/
static Portal
! exec_dynquery_with_params(PLpgSQL_execstate *estate, char *portalname,
! PLpgSQL_expr *dynquery,
! List *params,
! int cursorOption)
{
Portal portal;
Datum query;
***************
*** 5554,5573 ****
PreparedParamsData *ppd;
ppd = exec_eval_using_params(estate, params);
! portal = SPI_cursor_open_with_args(NULL,
querystr,
ppd->nargs, ppd->types,
ppd->values, ppd->nulls,
! estate->readonly_func, 0);
free_params_data(ppd);
}
else
{
! portal = SPI_cursor_open_with_args(NULL,
querystr,
0, NULL,
NULL, NULL,
! estate->readonly_func, 0);
}
if (portal == NULL)
--- 5523,5544 ----
PreparedParamsData *ppd;
ppd = exec_eval_using_params(estate, params);
! portal = SPI_cursor_open_with_args(portalname,
querystr,
ppd->nargs, ppd->types,
ppd->values, ppd->nulls,
! estate->readonly_func,
! cursorOption);
free_params_data(ppd);
}
else
{
! portal = SPI_cursor_open_with_args(portalname,
querystr,
0, NULL,
NULL, NULL,
! estate->readonly_func,
! cursorOption);
}
if (portal == NULL)
*** ./src/pl/plpgsql/src/pl_funcs.c.orig 2009-11-12 01:13:00.000000000 +0100
--- ./src/pl/plpgsql/src/pl_funcs.c 2009-11-17 21:03:12.172210054 +0100
***************
*** 619,627 ****
printf(" execute = '");
dump_expr(stmt->dynquery);
printf("'\n");
}
dump_indent -= 2;
-
}
static void
--- 619,646 ----
printf(" execute = '");
dump_expr(stmt->dynquery);
printf("'\n");
+
+ if (stmt->params != NIL)
+ {
+ ListCell *lc;
+ int i;
+
+ dump_indent += 2;
+ dump_ind();
+ printf(" USING\n");
+ dump_indent += 2;
+ i = 1;
+ foreach(lc, stmt->params)
+ {
+ dump_ind();
+ printf(" parameter $%d: ", i++);
+ dump_expr((PLpgSQL_expr *) lfirst(lc));
+ printf("\n");
+ }
+ dump_indent -= 4;
+ }
}
dump_indent -= 2;
}
static void
*** ./src/pl/plpgsql/src/plpgsql.h.orig 2009-11-13 23:43:42.000000000 +0100
--- ./src/pl/plpgsql/src/plpgsql.h 2009-11-17 16:35:35.075209934 +0100
***************
*** 503,508 ****
--- 503,509 ----
PLpgSQL_expr *argquery;
PLpgSQL_expr *query;
PLpgSQL_expr *dynquery;
+ List *params; /* USING expressions */
} PLpgSQL_stmt_open;
*** ./src/test/regress/expected/plpgsql.out.orig 2009-11-13 23:43:42.000000000 +0100
--- ./src/test/regress/expected/plpgsql.out 2009-11-17 20:11:14.000000000 +0100
***************
*** 3189,3194 ****
--- 3189,3223 ----
26
(1 row)
+ drop function exc_using(int, text);
+ create or replace function exc_using(int) returns void as $$
+ declare
+ c refcursor;
+ i int;
+ begin
+ open c for execute 'select * from generate_series(1,$1)' using $1+1;
+ loop
+ fetch c into i;
+ exit when not found;
+ raise notice '%', i;
+ end loop;
+ close c;
+ return;
+ end;
+ $$ language plpgsql;
+ select exc_using(5);
+ NOTICE: 1
+ NOTICE: 2
+ NOTICE: 3
+ NOTICE: 4
+ NOTICE: 5
+ NOTICE: 6
+ exc_using
+ -----------
+
+ (1 row)
+
+ drop function exc_using(int);
-- test FOR-over-cursor
create or replace function forc01() returns void as $$
declare
*** ./src/test/regress/sql/plpgsql.sql.orig 2009-11-13 23:43:42.000000000 +0100
--- ./src/test/regress/sql/plpgsql.sql 2009-11-17 20:09:57.030208080 +0100
***************
*** 2629,2634 ****
--- 2629,2656 ----
select exc_using(5, 'foobar');
+ drop function exc_using(int, text);
+
+ create or replace function exc_using(int) returns void as $$
+ declare
+ c refcursor;
+ i int;
+ begin
+ open c for execute 'select * from generate_series(1,$1)' using $1+1;
+ loop
+ fetch c into i;
+ exit when not found;
+ raise notice '%', i;
+ end loop;
+ close c;
+ return;
+ end;
+ $$ language plpgsql;
+
+ select exc_using(5);
+
+ drop function exc_using(int);
+
-- test FOR-over-cursor
create or replace function forc01() returns void as $$
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
