[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-patches
Subject: Re: [PATCHES] actualised execute using patch
From: "Pavel Stehule" <pavel.stehule () gmail ! com>
Date: 2008-03-26 7:01:57
Message-ID: 162867790803260001u41d59deesd67b28f477ffb5fa () mail ! gmail ! com
[Download RAW message or body]
On 25/03/2008, Zdenek Kotala <Zdenek.Kotala@sun.com> wrote:
> you have extra space onPavel Stehule napsal(a):
>
> > Hello
> >
> > http://archives.postgresql.org/pgsql-patches/2007-10/msg00161.php
> >
> > I actualized this patch for current CVS
>
>
> Hi Pavel,
>
> I tested your patch and it works regarding to proposal. However
> I have some small comments:
>
> 1) you have extra space in ./src/pl/plpgsql/src/gram.y on line 1351
I didn't find it
>
> 2) there is some Czech text in regression tests diffs
>
fixed
> 3) I would like to add two more tests:
>
> a) test which check if execute really takes 3rd parameter and inject it as a
> first one:
>
> create or replace function test(int,int,int) returns int as $$
> declare
> a int;
> begin
> execute 'select $1' into a using $3;
> return a;
> end
> $$ language plpgsql;
> select test(1,2,3);
>
> b) and second test which control number of arguments:
>
> for example:
>
> create or replace function test(int,int,int) returns int as $$
> begin
> execute 'select $3' into a using $1;
> return a;
> end
> $$ language plpgsql;
> select test(1,2,3);
>
I did it
>
>
>
> Zdenek
>
>
["using.diff" (text/x-patch)]
*** ./doc/src/sgml/plpgsql.sgml.orig 2008-03-23 01:24:19.000000000 +0100
--- ./doc/src/sgml/plpgsql.sgml 2008-03-24 20:41:27.000000000 +0100
***************
*** 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
***************
*** 1997,2003 ****
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>
--- 2012,2018 ----
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 2008-03-26 07:30:27.000000000 +0100
--- ./src/pl/plpgsql/src/gram.y 2008-03-24 20:41:27.000000000 +0100
***************
*** 21,26 ****
--- 21,27 ----
static PLpgSQL_expr *read_sql_construct(int until,
int until2,
+ int until3,
const char *expected,
const char *sqlstart,
bool isexpression,
***************
*** 200,205 ****
--- 201,207 ----
%token K_THEN
%token K_TO
%token K_TYPE
+ %token K_USING
%token K_WARNING
%token K_WHEN
%token K_WHILE
***************
*** 892,899 ****
{
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;
--- 894,907 ----
{
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;
***************
*** 920,925 ****
--- 928,948 ----
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;
}
***************
*** 954,959 ****
--- 977,983 ----
*/
expr1 = read_sql_construct(K_DOTDOT,
K_LOOP,
+ 0,
"LOOP",
"SELECT ",
true,
***************
*** 975,980 ****
--- 999,1005 ----
/* Read and check the second one */
expr2 = read_sql_construct(K_LOOP,
K_BY,
+ 0,
"LOOP",
"SELECT ",
true,
***************
*** 1222,1228 ****
for (;;)
{
! expr = read_sql_construct(',', ';', ", or ;",
"SELECT ",
true, true, &term);
new->params = lappend(new->params, expr);
--- 1247,1253 ----
for (;;)
{
! expr = read_sql_construct(',', ';', 0, ", or ;",
"SELECT ",
true, true, &term);
new->params = lappend(new->params, expr);
***************
*** 1307,1316 ****
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;
--- 1332,1341 ----
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;
***************
*** 1319,1334 ****
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;
}
;
--- 1344,1375 ----
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;
}
;
***************
*** 1733,1745 ****
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);
}
/*
--- 1774,1786 ----
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);
}
/*
***************
*** 1747,1752 ****
--- 1788,1794 ----
*
* 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"
***************
*** 1757,1762 ****
--- 1799,1805 ----
static PLpgSQL_expr *
read_sql_construct(int until,
int until2,
+ int until3,
const char *expected,
const char *sqlstart,
bool isexpression,
***************
*** 1783,1788 ****
--- 1826,1833 ----
break;
if (tok == until2 && parenlevel == 0)
break;
+ if (tok == until3 && parenlevel == 0)
+ break;
if (tok == '(' || tok == '[')
parenlevel++;
else if (tok == ')' || tok == ']')
***************
*** 2066,2079 ****
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;
}
--- 2111,2124 ----
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;
}
***************
*** 2088,2094 ****
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;
}
--- 2133,2139 ----
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;
}
***************
*** 2233,2239 ****
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;
}
--- 2278,2284 ----
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 2008-01-01 20:46:00.000000000 +0100
--- ./src/pl/plpgsql/src/pl_exec.c 2008-03-24 20:41:27.000000000 +0100
***************
*** 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);
/* ----------
***************
*** 2676,2686 ****
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:
--- 2690,2715 ----
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:
***************
*** 2856,2870 ****
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));
--- 2885,2915 ----
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 2008-01-01 20:46:00.000000000 +0100
--- ./src/pl/plpgsql/src/pl_funcs.c 2008-03-24 20:52:44.000000000 +0100
***************
*** 1007,1025 ****
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");
}
--- 1007,1058 ----
stmt->strict ? " STRICT" : "",
stmt->row->rowno, stmt->row->refname);
}
+ if (stmt->params != NULL)
+ {
+ ListCell *lc;
+ int i = 1;
+
+ 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 2008-03-24 20:40:16.000000000 +0100
--- ./src/pl/plpgsql/src/plpgsql.h 2008-03-24 20:41:27.000000000 +0100
***************
*** 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 2008-01-01 20:46:00.000000000 +0100
--- ./src/pl/plpgsql/src/scan.l 2008-03-24 20:41:27.000000000 +0100
***************
*** 161,166 ****
--- 161,167 ----
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 2008-03-24 20:40:40.000000000 +0100
--- ./src/test/regress/expected/plpgsql.out 2008-03-26 07:46:04.000000000 +0100
***************
*** 3128,3130 ****
--- 3128,3205 ----
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);
+ create or replace function exc_test(int,int,int) returns int as $$
+ declare
+ a int;
+ begin
+ execute 'select $1' into a using $3;
+ return a;
+ end
+ $$ language plpgsql;
+ select exc_test(1,2,3);
+ exc_test
+ ----------
+ 3
+ (1 row)
+
+ -- have to fail
+ create or replace function exc_test(int,int,int) returns int as $$
+ declare
+ a int;
+ begin
+ execute 'select $3' into a using $1;
+ return a;
+ end
+ $$ language plpgsql;
+ select exc_test(1,2,3);
+ ERROR: there is no parameter $3
+ CONTEXT: SQL statement "select $3"
+ PL/pgSQL function "exc_test" line 4 at EXECUTE statement
+ drop function exc_test(int,int,int);
*** ./src/test/regress/sql/plpgsql.sql.orig 2007-07-25 06:19:09.000000000 +0200
--- ./src/test/regress/sql/plpgsql.sql 2008-03-26 07:45:00.000000000 +0100
***************
*** 2580,2583 ****
end;
$$ language plpgsql;
! select * from ret_query2(8);
\ Chybí znak konce řádku na konci souboru
--- 2580,2643 ----
end;
$$ 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);
!
! create or replace function exc_test(int,int,int) returns int as $$
! declare
! a int;
! begin
! execute 'select $1' into a using $3;
! return a;
! end
! $$ language plpgsql;
! select exc_test(1,2,3);
!
! -- have to fail
! create or replace function exc_test(int,int,int) returns int as $$
! declare
! a int;
! begin
! execute 'select $3' into a using $1;
! return a;
! end
! $$ language plpgsql;
! select exc_test(1,2,3);
!
! drop function exc_test(int,int,int);
--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic