Hello
this patch add USING clause into plpgsql EXECUTE statements.
Proposal:
http://archives.postgresql.org/pgsql-hackers/2007-10/msg00790.php
I found, so dynamics statements are little bit faster with parameters,
because we don't need call lot of in out/in functions. Mainly it is
barier to SQL injection.
I have question, who will be commiter of plpgsql region? I am quite
irritated from 8.3 process. Bruce's patch queue more or less black
hole, and I have not any idea, if somebody checking my patches or not
and if I have to be in readiness or not.
Patch queue is longer and longer, and I need to know any responsible
person who can be recipient of my reminder request. Really it's
nothing nice, if your work is repeatedly deleted or inserted to
current queue. Nobody can do any plans.
Best regards
Pavel Stehule
*** ./doc/src/sgml/plpgsql.sgml.orig 2007-10-22 10:31:57.000000000 +0200
--- ./doc/src/sgml/plpgsql.sgml 2007-10-22 12:04:52.000000000 +0200
***************
*** 1005,1011 ****
<command>EXECUTE</command> statement is provided:
<synopsis>
! EXECUTE <replaceable class="command">command-string</replaceable> <optional> INTO <optional>STRICT</optional> <replaceable>target</replaceable> </optional>;
</synopsis>
where <replaceable>command-string</replaceable> is an expression
--- 1005,1011 ----
<command>EXECUTE</command> statement is provided:
<synopsis>
! EXECUTE <replaceable class="command">command-string</replaceable> <optional> INTO <optional>STRICT</optional> <replaceable>target</replaceable> </optional> <optional> USING <replaceable class="parameter">expression</replaceable> <optional>, ...</optional> </optional>;
</synopsis>
where <replaceable>command-string</replaceable> is an expression
***************
*** 1046,1051 ****
--- 1046,1066 ----
If the <literal>STRICT</> option is given, an error is reported
unless the query produces exactly one row.
</para>
+
+ <para>
+ The <command>EXECUTE</command> statement can take parameters. To refer
+ to the parameters use $1, $2, $3, etc. Any parameter have to be bind to
+ any variable or any expression with USING clause. You cannot use bind
+ arguments to pass the names of schema objects to a dynamic SQL statement.
+ The use of arguments is perfect protection from SQL injection.
+ <programlisting>
+ EXECUTE 'SELECT count(*) FROM '
+ || tabname::regclass
+ || ' WHERE inserted_by = $1 AND inserted <= $2'
+ INTO c
+ USING checked_user, checked_date;
+ </programlisting>
+ </para>
<para>
<command>SELECT INTO</command> is not currently supported within
***************
*** 1924,1930 ****
rows:
<synopsis>
<optional> <<<replaceable>label</replaceable>>> </optional>
! FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> LOOP
<replaceable>statements</replaceable>
END LOOP <optional> <replaceable>label</replaceable> </optional>;
</synopsis>
--- 1939,1945 ----
rows:
<synopsis>
<optional> <<<replaceable>label</replaceable>>> </optional>
! FOR <replaceable>target</replaceable> IN EXECUTE <replaceable>text_expression</replaceable> <optional> USING <replaceable class="parameter">expression</replaceable> <optional>, ...</optional> </optional> LOOP
<replaceable>statements</replaceable>
END LOOP <optional> <replaceable>label</replaceable> </optional>;
</synopsis>
*** ./src/pl/plpgsql/src/gram.y.orig 2007-10-16 10:44:47.000000000 +0200
--- ./src/pl/plpgsql/src/gram.y 2007-10-22 12:07:44.000000000 +0200
***************
*** 21,26 ****
--- 21,27 ----
static PLpgSQL_expr *read_sql_construct(int until,
int until2,
+ int until3,
const char *expected,
const char *sqlstart,
bool isexpression,
***************
*** 201,206 ****
--- 202,208 ----
%token K_THEN
%token K_TO
%token K_TYPE
+ %token K_USING
%token K_WARNING
%token K_WHEN
%token K_WHILE
***************
*** 893,900 ****
{
PLpgSQL_stmt_dynfors *new;
PLpgSQL_expr *expr;
! expr = plpgsql_read_expression(K_LOOP, "LOOP");
new = palloc0(sizeof(PLpgSQL_stmt_dynfors));
new->cmd_type = PLPGSQL_STMT_DYNFORS;
--- 895,908 ----
{
PLpgSQL_stmt_dynfors *new;
PLpgSQL_expr *expr;
+ int term;
! expr = read_sql_construct(K_LOOP,
! K_USING,
! 0,
! "LOOP|USING",
! "SELECT ",
! true, true, &term);
new = palloc0(sizeof(PLpgSQL_stmt_dynfors));
new->cmd_type = PLPGSQL_STMT_DYNFORS;
***************
*** 921,926 ****
--- 929,949 ----
yyerror("loop variable of loop over rows must be a record or row variable or list of scalar variables");
}
new->query = expr;
+
+ if (term == K_USING)
+ {
+ for(;;)
+ {
+ int term;
+
+ expr = read_sql_construct(',', K_LOOP, 0, ", or LOOP",
+ "SELECT ",
+ true, true, &term);
+ new->params = lappend(new->params, expr);
+ if (term == K_LOOP)
+ break;
+ }
+ }
$$ = (PLpgSQL_stmt *) new;
}
***************
*** 955,960 ****
--- 978,984 ----
*/
expr1 = read_sql_construct(K_DOTDOT,
K_LOOP,
+ 0,
"LOOP",
"SELECT ",
true,
***************
*** 976,981 ****
--- 1000,1006 ----
/* Read and check the second one */
expr2 = read_sql_construct(K_LOOP,
K_BY,
+ 0,
"LOOP",
"SELECT ",
true,
***************
*** 1215,1221 ****
for (;;)
{
! expr = read_sql_construct(',', ';', ", or ;",
"SELECT ",
true, true, &term);
new->params = lappend(new->params, expr);
--- 1240,1246 ----
for (;;)
{
! expr = read_sql_construct(',', ';', 0, ", or ;",
"SELECT ",
true, true, &term);
new->params = lappend(new->params, expr);
***************
*** 1300,1309 ****
PLpgSQL_expr *expr;
int endtoken;
! expr = read_sql_construct(K_INTO, ';', "INTO|;",
"SELECT ",
true, true, &endtoken);
!
new = palloc(sizeof(PLpgSQL_stmt_dynexecute));
new->cmd_type = PLPGSQL_STMT_DYNEXECUTE;
new->lineno = $2;
--- 1325,1334 ----
PLpgSQL_expr *expr;
int endtoken;
! expr = read_sql_construct(K_INTO, K_USING, ';', "INTO or USING or ;",
"SELECT ",
true, true, &endtoken);
!
new = palloc(sizeof(PLpgSQL_stmt_dynexecute));
new->cmd_type = PLPGSQL_STMT_DYNEXECUTE;
new->lineno = $2;
***************
*** 1312,1327 ****
new->strict = false;
new->rec = NULL;
new->row = NULL;
/* If we found "INTO", collect the argument */
if (endtoken == K_INTO)
{
new->into = true;
read_into_target(&new->rec, &new->row, &new->strict);
! if (yylex() != ';')
yyerror("syntax error");
}
!
$$ = (PLpgSQL_stmt *)new;
}
;
--- 1337,1368 ----
new->strict = false;
new->rec = NULL;
new->row = NULL;
+ new->params = NULL;
/* If we found "INTO", collect the argument */
if (endtoken == K_INTO)
{
new->into = true;
read_into_target(&new->rec, &new->row, &new->strict);
!
! endtoken = yylex();
! if (endtoken != ';' && endtoken != K_USING)
yyerror("syntax error");
}
!
! if (endtoken == K_USING)
! {
! for(;;)
! {
! expr = read_sql_construct(',', ';', 0, ", or ;",
! "SELECT ",
! true, true, &endtoken);
! new->params = lappend(new->params, expr);
! if (endtoken == ';')
! break;
! }
! }
!
$$ = (PLpgSQL_stmt *)new;
}
;
***************
*** 1341,1347 ****
if ($3->cursor_explicit_expr == NULL)
{
/* be nice if we could use opt_scrollable here */
! tok = yylex();
if (tok == K_NOSCROLL)
{
new->cursor_options |= CURSOR_OPT_NO_SCROLL;
--- 1382,1388 ----
if ($3->cursor_explicit_expr == NULL)
{
/* be nice if we could use opt_scrollable here */
! tok = yylex();
if (tok == K_NOSCROLL)
{
new->cursor_options |= CURSOR_OPT_NO_SCROLL;
***************
*** 1718,1730 ****
PLpgSQL_expr *
plpgsql_read_expression(int until, const char *expected)
{
! return read_sql_construct(until, 0, expected, "SELECT ", true, true, NULL);
}
static PLpgSQL_expr *
read_sql_stmt(const char *sqlstart)
{
! return read_sql_construct(';', 0, ";", sqlstart, false, true, NULL);
}
/*
--- 1759,1771 ----
PLpgSQL_expr *
plpgsql_read_expression(int until, const char *expected)
{
! return read_sql_construct(until, 0, 0, expected, "SELECT ", true, true, NULL);
}
static PLpgSQL_expr *
read_sql_stmt(const char *sqlstart)
{
! return read_sql_construct(';', 0, 0, ";", sqlstart, false, true, NULL);
}
/*
***************
*** 1732,1737 ****
--- 1773,1779 ----
*
* until: token code for expected terminator
* until2: token code for alternate terminator (pass 0 if none)
+ * until3: token code for alternate terminator (pass 0 if none)
* expected: text to use in complaining that terminator was not found
* sqlstart: text to prefix to the accumulated SQL text
* isexpression: whether to say we're reading an "expression" or a "statement"
***************
*** 1742,1747 ****
--- 1784,1790 ----
static PLpgSQL_expr *
read_sql_construct(int until,
int until2,
+ int until3,
const char *expected,
const char *sqlstart,
bool isexpression,
***************
*** 1768,1773 ****
--- 1811,1818 ----
break;
if (tok == until2 && parenlevel == 0)
break;
+ if (tok == until3 && parenlevel == 0)
+ break;
if (tok == '(' || tok == '[')
parenlevel++;
else if (tok == ')' || tok == ']')
***************
*** 2051,2064 ****
else if (pg_strcasecmp(yytext, "absolute") == 0)
{
fetch->direction = FETCH_ABSOLUTE;
! fetch->expr = read_sql_construct(K_FROM, K_IN, "FROM or IN",
"SELECT ", true, true, NULL);
check_FROM = false;
}
else if (pg_strcasecmp(yytext, "relative") == 0)
{
fetch->direction = FETCH_RELATIVE;
! fetch->expr = read_sql_construct(K_FROM, K_IN, "FROM or IN",
"SELECT ", true, true, NULL);
check_FROM = false;
}
--- 2096,2109 ----
else if (pg_strcasecmp(yytext, "absolute") == 0)
{
fetch->direction = FETCH_ABSOLUTE;
! fetch->expr = read_sql_construct(K_FROM, K_IN, 0, "FROM or IN",
"SELECT ", true, true, NULL);
check_FROM = false;
}
else if (pg_strcasecmp(yytext, "relative") == 0)
{
fetch->direction = FETCH_RELATIVE;
! fetch->expr = read_sql_construct(K_FROM, K_IN, 0, "FROM or IN",
"SELECT ", true, true, NULL);
check_FROM = false;
}
***************
*** 2073,2079 ****
else if (tok != T_SCALAR)
{
plpgsql_push_back_token(tok);
! fetch->expr = read_sql_construct(K_FROM, K_IN, "FROM or IN",
"SELECT ", true, true, NULL);
check_FROM = false;
}
--- 2118,2124 ----
else if (tok != T_SCALAR)
{
plpgsql_push_back_token(tok);
! fetch->expr = read_sql_construct(K_FROM, K_IN, 0, "FROM or IN",
"SELECT ", true, true, NULL);
check_FROM = false;
}
***************
*** 2218,2224 ****
new = palloc0(sizeof(PLpgSQL_stmt_return_query));
new->cmd_type = PLPGSQL_STMT_RETURN_QUERY;
new->lineno = lineno;
! new->query = read_sql_construct(';', 0, ")", "", false, true, NULL);
return (PLpgSQL_stmt *) new;
}
--- 2263,2269 ----
new = palloc0(sizeof(PLpgSQL_stmt_return_query));
new->cmd_type = PLPGSQL_STMT_RETURN_QUERY;
new->lineno = lineno;
! new->query = read_sql_construct(';',0, 0, ")", "", false, true, NULL);
return (PLpgSQL_stmt *) new;
}
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2007-10-16 14:50:13.000000000 +0200
--- ./src/pl/plpgsql/src/pl_exec.c 2007-10-21 08:16:04.000000000 +0200
***************
*** 33,38 ****
--- 33,39 ----
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/typcache.h"
+ #include "nodes/pg_list.h"
static const char *const raise_skip_msg = "RAISE";
***************
*** 65,70 ****
--- 66,81 ----
static SimpleEstateStackEntry *simple_estate_stack = NULL;
static long int simple_estate_id_counter = 0;
+ typedef struct PreparedParamsData
+ {
+ Datum *values; /* evaluated arguments */
+ bool *freevals; /* pfree-able arguments */
+ char *nulls; /* char array of null info */
+ Oid *types; /* types of arguments */
+ int nargs; /* number of arguments */
+ } PreparedParamsData;
+
+
/************************************************************
* Local function forward declarations
************************************************************/
***************
*** 177,182 ****
--- 188,196 ----
static void exec_set_found(PLpgSQL_execstate *estate, bool state);
static void plpgsql_create_econtext(PLpgSQL_execstate *estate);
static void free_var(PLpgSQL_var *var);
+ static PreparedParamsData *exec_eval_using_params(PLpgSQL_execstate *estate,
+ List *params);
+ static void free_params_data(PreparedParamsData *evp);
/* ----------
***************
*** 2675,2685 ****
exec_eval_cleanup(estate);
! /*
! * Call SPI_execute() without preparing a saved plan.
! */
! exec_res = SPI_execute(querystr, estate->readonly_func, 0);
switch (exec_res)
{
case SPI_OK_SELECT:
--- 2689,2714 ----
exec_eval_cleanup(estate);
! /* Evaluate and use using clause params */
! if (stmt->params)
! {
! void *plan;
!
! PreparedParamsData *up = exec_eval_using_params(estate, stmt->params);
!
! plan = SPI_prepare(querystr, up->nargs, up->types);
! if (plan == NULL)
! elog(ERROR, "SPI_prepare failed for \"%s\": %s",
! querystr, SPI_result_code_string(SPI_result));
+ exec_res = SPI_execute_plan(plan, up->values, up->nulls, estate->readonly_func, 0);
+
+ SPI_freeplan(plan);
+ free_params_data(up);
+ }
+ else
+ exec_res = SPI_execute(querystr, estate->readonly_func, 0);
+
switch (exec_res)
{
case SPI_OK_SELECT:
***************
*** 2855,2869 ****
exec_eval_cleanup(estate);
! /*
! * Prepare a plan and open an implicit cursor for the query
! */
! plan = SPI_prepare(querystr, 0, NULL);
! if (plan == NULL)
! elog(ERROR, "SPI_prepare failed for \"%s\": %s",
! querystr, SPI_result_code_string(SPI_result));
! portal = SPI_cursor_open(NULL, plan, NULL, NULL,
estate->readonly_func);
if (portal == NULL)
elog(ERROR, "could not open implicit cursor for query \"%s\": %s",
querystr, SPI_result_code_string(SPI_result));
--- 2884,2914 ----
exec_eval_cleanup(estate);
! /* Evaluate and use using clause params */
! if (stmt->params)
! {
! PreparedParamsData *up = exec_eval_using_params(estate, stmt->params);
!
! plan = SPI_prepare(querystr, up->nargs, up->types);
! if (plan == NULL)
! elog(ERROR, "SPI_prepare failed for \"%s\": %s",
! querystr, SPI_result_code_string(SPI_result));
!
! portal = SPI_cursor_open(NULL, plan, up->values, up->nulls,
! estate->readonly_func);
! free_params_data(up);
! }
! else
! {
! plan = SPI_prepare(querystr, 0, NULL);
! if (plan == NULL)
! elog(ERROR, "SPI_prepare failed for \"%s\": %s",
! querystr, SPI_result_code_string(SPI_result));
!
! portal = SPI_cursor_open(NULL, plan, NULL, NULL,
estate->readonly_func);
+ }
+
if (portal == NULL)
elog(ERROR, "could not open implicit cursor for query \"%s\": %s",
querystr, SPI_result_code_string(SPI_result));
***************
*** 5070,5072 ****
--- 5115,5194 ----
var->freeval = false;
}
}
+
+ /*
+ * exec_eval_using_params --- evaluate params of using clause
+ * EXECUTE INTO USING
+ */
+ static PreparedParamsData *
+ exec_eval_using_params(PLpgSQL_execstate *estate, List *params)
+ {
+ PreparedParamsData *up;
+ ListCell *current_param;
+ int i;
+ int nargs;
+
+
+ up = (PreparedParamsData *) palloc(sizeof(PreparedParamsData));
+ nargs = list_length(params);
+
+ up->types = (Oid *) palloc(nargs * sizeof(Oid));
+ up->values = (Datum *) palloc(nargs * sizeof(Datum));
+ up->nulls = (char *) palloc(nargs * sizeof(char));
+ up->freevals = (bool *) palloc(nargs * sizeof(bool));
+ up->nargs = nargs;
+
+ current_param = list_head(params);
+ for (i = 0; i < nargs; i++)
+ {
+ bool isnull;
+
+ up->freevals[i] = false;
+ up->values[i] = exec_eval_expr(estate,
+ (PLpgSQL_expr *) lfirst(current_param),
+ &isnull,
+ &up->types[i]);
+ up->nulls[i] = isnull ? 'n' : ' ';
+
+ /* pass-by-ref non null values must be copied into plpgsql context */
+ if (!isnull && OidIsValid(up->types[i]))
+ {
+ int16 typLen;
+ bool typByVal;
+
+ get_typlenbyval(up->types[i], &typLen, &typByVal);
+
+ if (!typByVal)
+ {
+ up->values[i] = datumCopy(up->values[i], typByVal, typLen);
+ up->freevals[i] = true;
+ }
+ }
+
+ current_param = lnext(current_param);
+
+ exec_eval_cleanup(estate);
+ }
+
+ return up;
+ }
+
+ /*
+ * free_params_data --- pfree all pass-by-reference values used in using clause
+ */
+ static void
+ free_params_data(PreparedParamsData *up)
+ {
+ int i;
+
+ for (i = 0; i < up->nargs; i++)
+ if (up->freevals[i])
+ pfree(DatumGetPointer(up->values[i]));
+
+ pfree(up->types);
+ pfree(up->values);
+ pfree(up->nulls);
+ pfree(up->freevals);
+
+ pfree(up);
+ }
*** ./src/pl/plpgsql/src/pl_funcs.c.orig 2007-10-21 08:17:39.000000000 +0200
--- ./src/pl/plpgsql/src/pl_funcs.c 2007-10-22 00:11:28.000000000 +0200
***************
*** 935,948 ****
stmt->strict ? " STRICT" : "",
stmt->row->rowno, stmt->row->refname);
}
- if (stmt->
-
dump_indent -= 2;
}
static void
dump_dynexecute(PLpgSQL_stmt_dynexecute *stmt)
{
dump_ind();
printf("EXECUTE ");
dump_expr(stmt->query);
--- 935,949 ----
stmt->strict ? " STRICT" : "",
stmt->row->rowno, stmt->row->refname);
}
dump_indent -= 2;
}
static void
dump_dynexecute(PLpgSQL_stmt_dynexecute *stmt)
{
+ ListCell *lc;
+ int i = 1;
+
dump_ind();
printf("EXECUTE ");
dump_expr(stmt->query);
***************
*** 963,981 ****
stmt->strict ? " STRICT" : "",
stmt->row->rowno, stmt->row->refname);
}
dump_indent -= 2;
}
static void
dump_dynfors(PLpgSQL_stmt_dynfors *stmt)
{
dump_ind();
printf("FORS %s EXECUTE ", (stmt->rec != NULL) ? stmt->rec->refname : stmt->row->refname);
dump_expr(stmt->query);
printf("\n");
!
dump_stmts(stmt->body);
-
dump_ind();
printf(" ENDFORS\n");
}
--- 964,1012 ----
stmt->strict ? " STRICT" : "",
stmt->row->rowno, stmt->row->refname);
}
+ if (stmt->params != NULL)
+ {
+ dump_ind();
+ printf(" USING\n");
+ dump_indent += 2;
+ foreach(lc, stmt->params)
+ {
+ dump_ind();
+ printf(" parameter %d: ", i++);
+ dump_expr((PLpgSQL_expr *) lfirst(lc));
+ printf("\n");
+ }
+ dump_indent -= 2;
+ }
dump_indent -= 2;
}
static void
dump_dynfors(PLpgSQL_stmt_dynfors *stmt)
{
+ ListCell *lc;
+ int i = 1;
+
dump_ind();
printf("FORS %s EXECUTE ", (stmt->rec != NULL) ? stmt->rec->refname : stmt->row->refname);
dump_expr(stmt->query);
printf("\n");
! if (stmt->params != NULL)
! {
! dump_indent += 2;
! dump_ind();
! printf(" USING\n");
! dump_indent += 2;
! foreach(lc, stmt->params)
! {
! dump_ind();
! printf(" parameter $%d: ", i++);
! dump_expr((PLpgSQL_expr *) lfirst(lc));
! printf("\n");
! }
! dump_indent -= 4;
! }
dump_stmts(stmt->body);
dump_ind();
printf(" ENDFORS\n");
}
*** ./src/pl/plpgsql/src/plpgsql.h.orig 2007-10-16 10:45:10.000000000 +0200
--- ./src/pl/plpgsql/src/plpgsql.h 2007-10-16 14:45:04.000000000 +0200
***************
*** 430,435 ****
--- 430,436 ----
PLpgSQL_row *row;
PLpgSQL_expr *query;
List *body; /* List of statements */
+ List *params; /* USING params */
} PLpgSQL_stmt_dynfors;
***************
*** 534,539 ****
--- 535,541 ----
bool strict; /* INTO STRICT flag */
PLpgSQL_rec *rec; /* INTO target, if record */
PLpgSQL_row *row; /* INTO target, if row */
+ List *params; /* USING params */
} PLpgSQL_stmt_dynexecute;
*** ./src/pl/plpgsql/src/scan.l.orig 2007-10-16 10:44:53.000000000 +0200
--- ./src/pl/plpgsql/src/scan.l 2007-10-16 10:48:23.000000000 +0200
***************
*** 163,168 ****
--- 163,169 ----
then { return K_THEN; }
to { return K_TO; }
type { return K_TYPE; }
+ using { return K_USING; }
warning { return K_WARNING; }
when { return K_WHEN; }
while { return K_WHILE; }
*** ./src/test/regress/expected/plpgsql.out.orig 2007-10-21 08:08:37.000000000 +0200
--- ./src/test/regress/expected/plpgsql.out 2007-10-21 08:07:07.000000000 +0200
***************
*** 3128,3130 ****
--- 3128,3177 ----
c9f0f895fb98ab9159f51fd0297e236d | 8 | t
(9 rows)
+ -- test EXECUTE USING
+ create function exc_using(int)
+ returns int
+ as $$
+ declare i int;
+ begin
+ for i in execute 'select * from ' || 'generate_series' || '(1,$1)' using $1 loop
+ raise notice '%', i;
+ end loop;
+ execute 'select $1+$1*3' into i using $1;
+ return i;
+ end
+ $$ language plpgsql;
+ select exc_using(5);
+ NOTICE: 1
+ NOTICE: 2
+ NOTICE: 3
+ NOTICE: 4
+ NOTICE: 5
+ exc_using
+ -----------
+ 20
+ (1 row)
+
+ drop function exc_using(int);
+ -- same for varlena type
+ create function exc_using(varchar)
+ returns setof varchar
+ as $$
+ declare v varchar;
+ begin
+ for v in execute 'select roomno from '|| 'WSlot'::regclass || ' where slotname = $1' using $1 loop
+ return next v;
+ end loop;
+ execute 'select upper($1)' into v using $1;
+ return next v;
+ return;
+ end
+ $$ language plpgsql;
+ select * from exc_using('WS.001.1b');
+ exc_using
+ -----------
+ 001
+ WS.001.1B
+ (2 rows)
+
+ drop function exc_using(varchar);
*** ./src/test/regress/sql/plpgsql.sql.orig 2007-10-22 12:09:17.000000000 +0200
--- ./src/test/regress/sql/plpgsql.sql 2007-10-21 08:06:27.000000000 +0200
***************
*** 2581,2583 ****
--- 2581,2620 ----
$$ language plpgsql;
select * from ret_query2(8);
+
+ -- test EXECUTE USING
+ create function exc_using(int)
+ returns int
+ as $$
+ declare i int;
+ begin
+ for i in execute 'select * from ' || 'generate_series' || '(1,$1)' using $1 loop
+ raise notice '%', i;
+ end loop;
+ execute 'select $1+$1*3' into i using $1;
+ return i;
+ end
+ $$ language plpgsql;
+
+ select exc_using(5);
+
+ drop function exc_using(int);
+
+ -- same for varlena type
+ create function exc_using(varchar)
+ returns setof varchar
+ as $$
+ declare v varchar;
+ begin
+ for v in execute 'select roomno from '|| 'WSlot'::regclass || ' where slotname = $1' using $1 loop
+ return next v;
+ end loop;
+ execute 'select upper($1)' into v using $1;
+ return next v;
+ return;
+ end
+ $$ language plpgsql;
+
+ select * from exc_using('WS.001.1b');
+
+ drop function exc_using(varchar);
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
