From 309cd7cf183d7e194205d8d97186c09ef67f83ae Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 19 Jan 2010 01:35:31 +0000 Subject: [PATCH] Add "USING expressions" option to plpgsql's OPEN cursor FOR EXECUTE. This is the last EXECUTE-like plpgsql statement that was missing the capability of inserting parameter values via USING. Pavel Stehule, reviewed by Itagaki Takahiro --- doc/src/sgml/plpgsql.sgml | 21 +++++--- src/pl/plpgsql/src/gram.y | 24 ++++++++- src/pl/plpgsql/src/pl_exec.c | 70 +++++++++------------------ src/pl/plpgsql/src/pl_funcs.c | 23 ++++++++- src/pl/plpgsql/src/plpgsql.h | 3 +- src/test/regress/expected/plpgsql.out | 29 +++++++++++ src/test/regress/sql/plpgsql.sql | 22 +++++++++ 7 files changed, 131 insertions(+), 61 deletions(-) diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 493e96e866..af8e372fe8 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,4 +1,4 @@ - + <application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language @@ -1016,7 +1016,7 @@ END; EXECUTE statement is provided: -EXECUTE command-string INTO STRICT target USING expression , ... ; +EXECUTE command-string INTO STRICT target USING expression , ... ; where command-string is an expression @@ -1500,7 +1500,7 @@ RETURN expression; RETURN NEXT expression; RETURN QUERY query; -RETURN QUERY EXECUTE command-string USING expression , ... ; +RETURN QUERY EXECUTE command-string USING expression , ... ; @@ -2190,7 +2190,7 @@ $$ LANGUAGE plpgsql; rows: <<label>> -FOR target IN EXECUTE text_expression USING expression , ... LOOP +FOR target IN EXECUTE text_expression USING expression , ... LOOP statements END LOOP label ; @@ -2495,7 +2495,7 @@ OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey; <command>OPEN FOR EXECUTE</command> -OPEN unbound_cursorvar NO SCROLL FOR EXECUTE query_string; +OPEN unbound_cursorvar NO SCROLL FOR EXECUTE query_string USING expression , ... ; @@ -2507,7 +2507,8 @@ OPEN unbound_cursorvar NO ), and it also means that variable substitution is not done on the - command string. + command string. As with EXECUTE, parameter values + can be inserted into the dynamic command via USING. The SCROLL and NO SCROLL options have the same meanings as for a bound cursor. @@ -2516,8 +2517,12 @@ OPEN unbound_cursorvar NO An example: -OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1); +OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident(tabname) ' WHERE col1 = $1' USING keyvalue; + In this example, the table name is inserted into the query textually, + so use of quote_ident() is recommended to guard against + SQL injection. The comparison value for col1 is inserted + via a USING parameter, so it needs no quoting. @@ -2893,7 +2898,7 @@ END LOOP label ; raise errors. -RAISE level 'format' , expression , ... USING option = expression , ... ; +RAISE level 'format' , expression , ... USING option = expression , ... ; RAISE level condition_name USING option = expression , ... ; RAISE level SQLSTATE 'sqlstate' USING option = expression , ... ; RAISE level USING option = expression , ... ; diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y index c4e3c12834..46500ad58c 100644 --- a/src/pl/plpgsql/src/gram.y +++ b/src/pl/plpgsql/src/gram.y @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.139 2010/01/10 17:56:50 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.140 2010/01/19 01:35:30 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -1704,7 +1704,27 @@ stmt_open : K_OPEN cursor_variable tok = yylex(); if (tok == K_EXECUTE) { - new->dynquery = read_sql_stmt("SELECT "); + int endtoken; + + new->dynquery = + read_sql_expression2(K_USING, ';', + "USING or ;", + &endtoken); + + /* If we found "USING", collect 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 { diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index 4139126b48..e15076a5a8 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.253 2010/01/02 16:58:13 momjian Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.254 2010/01/19 01:35:31 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -200,7 +200,8 @@ 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); + PLpgSQL_expr *dynquery, List *params, + const char *portalname, int cursorOptions); /* ---------- @@ -2337,7 +2338,7 @@ exec_stmt_return_query(PLpgSQL_execstate *estate, /* RETURN QUERY EXECUTE */ Assert(stmt->dynquery != NULL); portal = exec_dynquery_with_params(estate, stmt->dynquery, - stmt->params); + stmt->params, NULL, 0); } tupmap = convert_tuples_by_position(portal->tupDesc, @@ -3133,7 +3134,8 @@ exec_stmt_dynfors(PLpgSQL_execstate *estate, PLpgSQL_stmt_dynfors *stmt) Portal portal; int rc; - portal = exec_dynquery_with_params(estate, stmt->query, stmt->params); + portal = exec_dynquery_with_params(estate, stmt->query, stmt->params, + NULL, 0); /* * Execute the loop @@ -3161,7 +3163,6 @@ exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt) PLpgSQL_expr *query; Portal portal; ParamListInfo paramLI; - bool isnull; /* ---------- * 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) * 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); + portal = exec_dynquery_with_params(estate, + stmt->dynquery, + stmt->params, + curname, + stmt->cursor_options); /* * If cursor variable was NULL, store the generated portal name in it @@ -5530,8 +5499,11 @@ free_params_data(PreparedParamsData *ppd) * Open portal for dynamic query */ static Portal -exec_dynquery_with_params(PLpgSQL_execstate *estate, PLpgSQL_expr *dynquery, - List *params) +exec_dynquery_with_params(PLpgSQL_execstate *estate, + PLpgSQL_expr *dynquery, + List *params, + const char *portalname, + int cursorOptions) { Portal portal; Datum query; @@ -5564,20 +5536,22 @@ exec_dynquery_with_params(PLpgSQL_execstate *estate, PLpgSQL_expr *dynquery, PreparedParamsData *ppd; ppd = exec_eval_using_params(estate, params); - portal = SPI_cursor_open_with_args(NULL, + portal = SPI_cursor_open_with_args(portalname, querystr, ppd->nargs, ppd->types, ppd->values, ppd->nulls, - estate->readonly_func, 0); + estate->readonly_func, + cursorOptions); free_params_data(ppd); } else { - portal = SPI_cursor_open_with_args(NULL, + portal = SPI_cursor_open_with_args(portalname, querystr, 0, NULL, NULL, NULL, - estate->readonly_func, 0); + estate->readonly_func, + cursorOptions); } if (portal == NULL) diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c index 9e7b1794b9..a8d0664b20 100644 --- a/src/pl/plpgsql/src/pl_funcs.c +++ b/src/pl/plpgsql/src/pl_funcs.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.87 2010/01/02 16:58:13 momjian Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.88 2010/01/19 01:35:31 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -619,9 +619,28 @@ dump_open(PLpgSQL_stmt_open *stmt) 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 diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h index 4601a4f813..2aba853746 100644 --- a/src/pl/plpgsql/src/plpgsql.h +++ b/src/pl/plpgsql/src/plpgsql.h @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.128 2010/01/10 17:15:18 tgl Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.129 2010/01/19 01:35:31 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -503,6 +503,7 @@ typedef struct PLpgSQL_expr *argquery; PLpgSQL_expr *query; PLpgSQL_expr *dynquery; + List *params; /* USING expressions */ } PLpgSQL_stmt_open; diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index e126f616fe..c2bf41d6fc 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -3189,6 +3189,35 @@ NOTICE: 6 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 diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index 79756f6a01..3613194fd9 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -2629,6 +2629,28 @@ $$ language plpgsql; 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 $$ -- 2.40.0