Pavel Stehule wrote:
> > > There are some problems about replacing string values in the SQL string.
> >
> >Doesn't the Oracle implementation already imply a solution to that?
> >
>
> I don't know. I didn't find any detail documentation about it. I don't know
> what Oracle exactly do.
Oracle does use USING:
EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[, define_variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument]...]
[{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];
so I think we are OK there.
> >I think we'd be best off to leave EXECUTE alone, at least until we've
> >converged to the point where almost nobody is using non-standard-compliant
> >strings.
> >
>
> Maybe, but patch have to solve SQL string and non SQL strings too
The only case I see you using it is for \:. What is the purpose of
that? Can't we use :: for a literal :?
I have attached the patch from March.
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
*** ./src/pl/plpgsql/src/gram.y.orig 2006-03-26 09:53:27.000000000 +0200
--- ./src/pl/plpgsql/src/gram.y 2006-03-27 20:51:50.000000000 +0200
***************
*** 20,25 ****
--- 20,26 ----
static PLpgSQL_expr *read_sql_construct(int until,
int until2,
+ int until3,
const char *expected,
const char *sqlstart,
bool isexpression,
***************
*** 187,192 ****
--- 188,194 ----
%token K_THEN
%token K_TO
%token K_TYPE
+ %token K_USING
%token K_WARNING
%token K_WHEN
%token K_WHILE
***************
*** 858,869 ****
{
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;
new->lineno = $1;
if ($2.rec)
{
new->rec = $2.rec;
--- 860,874 ----
{
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;
new->lineno = $1;
+ new->params = NULL;
+
if ($2.rec)
{
new->rec = $2.rec;
***************
*** 886,891 ****
--- 891,909 ----
yyerror("loop variable of loop over rows must be a record or row variable or list of
scalarvariables");
}
new->query = expr;
+
+ if (term == K_USING)
+ {
+ for(;;)
+ {
+ 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;
}
***************
*** 920,925 ****
--- 938,944 ----
*/
expr1 = read_sql_construct(K_DOTDOT,
K_LOOP,
+ 0,
"LOOP",
"SELECT ",
true,
***************
*** 1262,1268 ****
for (;;)
{
! expr = read_sql_construct(',', ';', ", or ;",
"SELECT ",
true, true, &term);
new->params = lappend(new->params, expr);
--- 1281,1287 ----
for (;;)
{
! expr = read_sql_construct(',', ';', 0, ", or ;",
"SELECT ",
true, true, &term);
new->params = lappend(new->params, expr);
***************
*** 1332,1339 ****
PLpgSQL_stmt_dynexecute *new;
PLpgSQL_expr *expr;
int endtoken;
! expr = read_sql_construct(K_INTO, ';', "INTO|;", "SELECT ",
true, true, &endtoken);
new = palloc(sizeof(PLpgSQL_stmt_dynexecute));
--- 1351,1360 ----
PLpgSQL_stmt_dynexecute *new;
PLpgSQL_expr *expr;
int endtoken;
+ bool have_into;
+ bool have_using;
! expr = read_sql_construct(K_INTO, K_USING, ';', "INTO|USING|;", "SELECT ",
true, true, &endtoken);
new = palloc(sizeof(PLpgSQL_stmt_dynexecute));
***************
*** 1342,1380 ****
new->query = expr;
new->rec = NULL;
new->row = NULL;
/*
* If we saw "INTO", look for a following row
* var, record var, or list of scalars.
*/
! if (endtoken == K_INTO)
{
! switch (yylex())
{
! case T_ROW:
! new->row = yylval.row;
! check_assignable((PLpgSQL_datum *) new->row);
! break;
!
! case T_RECORD:
! new->rec = yylval.rec;
! check_assignable((PLpgSQL_datum *) new->rec);
break;
! case T_SCALAR:
! new->row = read_into_scalar_list(yytext, yylval.scalar);
break;
-
default:
! plpgsql_error_lineno = $2;
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("syntax error at \"%s\"", yytext),
! errdetail("Expected record variable, row variable, "
! "or list of scalar variables.")));
}
! if (yylex() != ';')
! yyerror("syntax error");
}
$$ = (PLpgSQL_stmt *)new;
--- 1363,1447 ----
new->query = expr;
new->rec = NULL;
new->row = NULL;
+ new->params = NULL;
+
+ have_into = false;
+ have_using = false;
/*
* If we saw "INTO", look for a following row
* var, record var, or list of scalars.
*/
!
! while (endtoken != ';')
{
! PLpgSQL_expr *expr;
! int term;
!
! switch (endtoken)
{
! case K_INTO:
! if (have_into)
! {
! plpgsql_error_lineno = plpgsql_scanner_lineno();
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("INTO specified more than once")));
! }
! switch (yylex())
! {
! case T_ROW:
! new->row = yylval.row;
! check_assignable((PLpgSQL_datum *) new->row);
! break;
!
! case T_RECORD:
! new->rec = yylval.rec;
! check_assignable((PLpgSQL_datum *) new->rec);
! break;
!
! case T_SCALAR:
! new->row = read_into_scalar_list(yytext, yylval.scalar);
! break;
!
! default:
! plpgsql_error_lineno = $2;
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("syntax error at \"%s\"", yytext),
! errdetail("Expected record variable, row variable, "
! "or list of scalar variables.")));
! }
!
! have_into = true;
! endtoken = yylex();
break;
+
+ case K_USING:
+ if (have_using)
+ {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("USING specified more than once")));
+ }
! for(;;)
! {
! expr = read_sql_construct(',', ';', K_INTO, ", or ; or INTO",
! "SELECT ",
! true, true, &term);
! new->params = lappend(new->params, expr);
! if (term != ',')
! break;
! }
! endtoken = term;
break;
default:
! yyerror("syntax error");
!
}
!
}
$$ = (PLpgSQL_stmt *)new;
***************
*** 1391,1400 ****
new->cmd_type = PLPGSQL_STMT_OPEN;
new->lineno = $2;
new->curvar = $3->varno;
if ($3->cursor_explicit_expr == NULL)
{
! tok = yylex();
if (tok != K_FOR)
{
plpgsql_error_lineno = $2;
--- 1458,1468 ----
new->cmd_type = PLPGSQL_STMT_OPEN;
new->lineno = $2;
new->curvar = $3->varno;
+ new->params = NULL;
if ($3->cursor_explicit_expr == NULL)
{
! tok = yylex();
if (tok != K_FOR)
{
plpgsql_error_lineno = $2;
***************
*** 1407,1414 ****
tok = yylex();
if (tok == K_EXECUTE)
! {
! new->dynquery = read_sql_stmt("SELECT ");
}
else
{
--- 1475,1500 ----
tok = yylex();
if (tok == K_EXECUTE)
! {
! PLpgSQL_expr *expr;
! int term;
!
! new->dynquery = read_sql_construct(';', K_USING, 0, "; or USING",
! "SELECT ",
! false,
! true,
! &term);
!
! if (term == K_USING)
! for(;;)
! {
! expr = read_sql_construct(',', ';', 0, ", or ;",
! "SELECT ",
! true, true, &term);
! new->params = lappend(new->params, expr);
! if (term == ';')
! break;
! }
}
else
{
***************
*** 1717,1729 ****
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);
}
/*
--- 1803,1816 ----
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);
}
/*
***************
*** 1741,1746 ****
--- 1828,1834 ----
static PLpgSQL_expr *
read_sql_construct(int until,
int until2,
+ int until3,
const char *expected,
const char *sqlstart,
bool isexpression,
***************
*** 1763,1772 ****
--- 1851,1863 ----
for (;;)
{
tok = yylex();
+
if (tok == until && parenlevel == 0)
break;
if (tok == until2 && parenlevel == 0)
break;
+ if (tok == until3 && parenlevel == 0)
+ break;
if (tok == '(' || tok == '[')
parenlevel++;
else if (tok == ')' || tok == ']')
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2006-03-09 22:29:36.000000000 +0100
--- ./src/pl/plpgsql/src/pl_exec.c 2006-03-27 21:04:51.000000000 +0200
***************
*** 155,160 ****
--- 155,165 ----
static bool compatible_tupdesc(TupleDesc td1, TupleDesc td2);
static void exec_set_found(PLpgSQL_execstate *estate, bool state);
static void free_var(PLpgSQL_var *var);
+ static char *replace_placeholders(PLpgSQL_execstate *estate,
+ char mode,
+ char *ctrlstr,
+ List *params,
+ char *command);
/* ----------
***************
*** 2015,2078 ****
static int
exec_stmt_raise(PLpgSQL_execstate *estate, PLpgSQL_stmt_raise *stmt)
{
! char *cp;
! PLpgSQL_dstring ds;
! ListCell *current_param;
!
! plpgsql_dstring_init(&ds);
! current_param = list_head(stmt->params);
!
! for (cp = stmt->message; *cp; cp++)
! {
! /*
! * Occurrences of a single % are replaced by the next parameter's
! * external representation. Double %'s are converted to one %.
! */
! if (cp[0] == '%')
! {
! Oid paramtypeid;
! Datum paramvalue;
! bool paramisnull;
! char *extval;
!
! if (cp[1] == '%')
! {
! plpgsql_dstring_append_char(&ds, cp[1]);
! cp++;
! continue;
! }
!
! if (current_param == NULL)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("too few parameters specified for RAISE")));
!
! paramvalue = exec_eval_expr(estate,
! (PLpgSQL_expr *) lfirst(current_param),
! ¶misnull,
! ¶mtypeid);
! if (paramisnull)
! extval = "<NULL>";
! else
! extval = convert_value_to_string(paramvalue, paramtypeid);
! plpgsql_dstring_append(&ds, extval);
! current_param = lnext(current_param);
! exec_eval_cleanup(estate);
! continue;
! }
!
! plpgsql_dstring_append_char(&ds, cp[0]);
! }
!
! /*
! * If more parameters were specified than were required to process the
! * format string, throw an error
! */
! if (current_param != NULL)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("too many parameters specified for RAISE")));
/*
* Throw the error (may or may not come back)
--- 2020,2028 ----
static int
exec_stmt_raise(PLpgSQL_execstate *estate, PLpgSQL_stmt_raise *stmt)
{
! char *message;
! message = replace_placeholders(estate, '%', stmt->message, stmt->params, "RAISE");
/*
* Throw the error (may or may not come back)
***************
*** 2081,2092 ****
ereport(stmt->elog_level,
((stmt->elog_level >= ERROR) ? errcode(ERRCODE_RAISE_EXCEPTION) : 0,
! errmsg_internal("%s", plpgsql_dstring_get(&ds))));
estate->err_text = NULL; /* un-suppress... */
!
! plpgsql_dstring_free(&ds);
!
return PLPGSQL_RC_OK;
}
--- 2031,2042 ----
ereport(stmt->elog_level,
((stmt->elog_level >= ERROR) ? errcode(ERRCODE_RAISE_EXCEPTION) : 0,
! errmsg_internal("%s", message)));
estate->err_text = NULL; /* un-suppress... */
!
! pfree(message);
!
return PLPGSQL_RC_OK;
}
***************
*** 2351,2356 ****
--- 2301,2308 ----
int exec_res;
PLpgSQL_rec *rec = NULL;
PLpgSQL_row *row = NULL;
+ char *exec_querystr;
+
if (stmt->rec != NULL)
rec = (PLpgSQL_rec *) (estate->datums[stmt->rec->recno]);
***************
*** 2372,2383 ****
exec_eval_cleanup(estate);
/*
* Call SPI_execute() without preparing a saved plan. The returncode can
* be any standard OK. Note that while a SELECT is allowed, its results
* will be discarded unless an INTO clause is specified.
*/
! exec_res = SPI_execute(querystr, estate->readonly_func, 0);
/* Assign to INTO variable */
if (rec || row)
--- 2324,2340 ----
exec_eval_cleanup(estate);
+ /* Second, we substitute placeholders */
+ exec_querystr = replace_placeholders(estate, ':', querystr, stmt->params, "EXECUTE USING");
+ pfree(querystr);
+
/*
* Call SPI_execute() without preparing a saved plan. The returncode can
* be any standard OK. Note that while a SELECT is allowed, its results
* will be discarded unless an INTO clause is specified.
*/
!
! exec_res = SPI_execute(exec_querystr, estate->readonly_func, 0);
/* Assign to INTO variable */
if (rec || row)
***************
*** 2461,2467 ****
/* Release any result from SPI_execute, as well as the querystring */
SPI_freetuptable(SPI_tuptable);
! pfree(querystr);
/* Save result info for GET DIAGNOSTICS */
estate->eval_processed = SPI_processed;
--- 2418,2424 ----
/* Release any result from SPI_execute, as well as the querystring */
SPI_freetuptable(SPI_tuptable);
! pfree(exec_querystr);
/* Save result info for GET DIAGNOSTICS */
estate->eval_processed = SPI_processed;
***************
*** 2492,2498 ****
void *plan;
Portal portal;
bool found = false;
!
/*
* Determine if we assign to a record or a row
*/
--- 2449,2455 ----
void *plan;
Portal portal;
bool found = false;
! char *exec_querystr;
/*
* Determine if we assign to a record or a row
*/
***************
*** 2518,2527 ****
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));
--- 2475,2487 ----
exec_eval_cleanup(estate);
+ exec_querystr = replace_placeholders(estate, ':', querystr, stmt->params, "EXECUTE USING");
+ pfree(querystr);
+
/*
* Prepare a plan and open an implicit cursor for the query
*/
! plan = SPI_prepare(exec_querystr, 0, NULL);
if (plan == NULL)
elog(ERROR, "SPI_prepare failed for \"%s\": %s",
querystr, SPI_result_code_string(SPI_result));
***************
*** 2530,2536 ****
if (portal == NULL)
elog(ERROR, "could not open implicit cursor for query \"%s\": %s",
querystr, SPI_result_code_string(SPI_result));
! pfree(querystr);
SPI_freeplan(plan);
/*
--- 2490,2496 ----
if (portal == NULL)
elog(ERROR, "could not open implicit cursor for query \"%s\": %s",
querystr, SPI_result_code_string(SPI_result));
! pfree(exec_querystr);
SPI_freeplan(plan);
/*
***************
*** 2714,2719 ****
--- 2674,2680 ----
Oid restype;
char *querystr;
void *curplan;
+ char *exec_querystr;
/* ----------
* We evaluate the string expression after the
***************
*** 2732,2742 ****
exec_eval_cleanup(estate);
/* ----------
* Now we prepare a query plan for it and open a cursor
* ----------
*/
! curplan = SPI_prepare(querystr, 0, NULL);
if (curplan == NULL)
elog(ERROR, "SPI_prepare failed for \"%s\": %s",
querystr, SPI_result_code_string(SPI_result));
--- 2693,2706 ----
exec_eval_cleanup(estate);
+ exec_querystr = replace_placeholders(estate, ':', querystr, stmt->params, "EXECUTE USING");
+ pfree(querystr);
+
/* ----------
* Now we prepare a query plan for it and open a cursor
* ----------
*/
! curplan = SPI_prepare(exec_querystr, 0, NULL);
if (curplan == NULL)
elog(ERROR, "SPI_prepare failed for \"%s\": %s",
querystr, SPI_result_code_string(SPI_result));
***************
*** 2745,2751 ****
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);
/* ----------
--- 2709,2715 ----
if (portal == NULL)
elog(ERROR, "could not open cursor for query \"%s\": %s",
querystr, SPI_result_code_string(SPI_result));
! pfree(exec_querystr);
SPI_freeplan(curplan);
/* ----------
***************
*** 4504,4506 ****
--- 4468,4640 ----
var->freeval = false;
}
}
+
+
+ /*
+ * Replace placeholders by positional parameters. Know two types of
+ * placeholders: raise_place_holder (symbol '%') and using_place_holder
+ * (symbol ':').
+ */
+
+ static char*
+ replace_placeholders(PLpgSQL_execstate *estate,
+ char mode,
+ char *ctrlstr,
+ List *params,
+ char *command)
+ {
+ PLpgSQL_dstring ds;
+ ListCell *current_param;
+ char *cp;
+ char *result;
+ bool in_str = false;
+ bool in_identif = false;
+
+ plpgsql_dstring_init(&ds);
+ current_param = list_head(params);
+
+ for (cp = ctrlstr; *cp; cp++)
+ {
+ if (mode == ':' && cp[0] == '\\' && cp[1] == ':')
+ {
+ /* solution for \: */
+ plpgsql_dstring_append_char(&ds, ':');
+ cp++;
+ continue;
+
+ }
+ if (cp[0] == mode)
+ {
+ Oid paramtypeid;
+ Datum paramvalue;
+ bool paramisnull;
+ char *extval;
+ int i;
+
+
+ if (mode == '%' && cp[1] == '%')
+ {
+ /* solution for %% */
+ plpgsql_dstring_append_char(&ds, cp[1]);
+ cp++;
+ continue;
+ }
+
+ if (cp[0] == ':' && cp[1] == ':')
+ {
+ /* solution for :: */
+ plpgsql_dstring_append(&ds, "::");
+ cp++;
+ continue;
+ }
+
+ /* check and skip position holder in dynamic sql statement */
+
+ if (mode == ':')
+ {
+ for(i = 1; cp[i] != '\0'; i++)
+ {
+ int c = cp[i];
+
+ if (('a' <= c && c <= 'z') ||
+ ('A' <= c && c <= 'Z') ||
+ ('0' <= c && c <= '9') ||
+ (c == '_') || (0200 <= c && c <= 0377))
+ continue;
+ break;
+ }
+
+ if (i == 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("Wrong position holder identifier in dynamic sql command")));
+ else
+ cp += i - 1;
+ }
+ if (current_param == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("too few parameters specified for %s", command)));
+
+ paramvalue = exec_eval_expr(estate,
+ (PLpgSQL_expr *) lfirst(current_param),
+ ¶misnull,
+ ¶mtypeid);
+
+ if (paramisnull)
+ {
+ extval = mode == '%' ? "<NULL>" : " NULL ";
+ plpgsql_dstring_append(&ds, extval);
+ }
+ else
+ {
+ char separator;
+
+ extval = convert_value_to_string(paramvalue, paramtypeid);
+
+ switch (mode)
+ {
+ case '%':
+ plpgsql_dstring_append(&ds, extval);
+ break;
+
+ case ':':
+ if (in_str || in_identif)
+ plpgsql_dstring_append(&ds, extval);
+ else
+ {
+ switch (paramtypeid)
+ {
+ case INT2OID:
+ case INT4OID:
+ case INT8OID:
+ case FLOAT4OID:
+ case FLOAT8OID:
+ case NUMERICOID:
+ case REGCLASSOID:
+ separator = ' ';
+ break;
+
+ default:
+ separator = '\'';
+ break;
+ }
+
+ plpgsql_dstring_append_char(&ds, separator);
+ plpgsql_dstring_append(&ds, extval);
+ plpgsql_dstring_append_char(&ds, separator);
+ }
+ break;
+ }
+ }
+
+ current_param = lnext(current_param);
+ exec_eval_cleanup(estate);
+
+ continue;
+ }
+ else
+ {
+ plpgsql_dstring_append_char(&ds, cp[0]);
+ if (cp[0] == '\'')
+ in_str = !in_str;
+
+ if (cp[0] == '"')
+ in_identif = !in_identif;
+ }
+ }
+
+ /*
+ * If more parameters were specified than were required to process the
+ * format string, throw an error
+ */
+ if (current_param != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("too many parameters specified for %s", command)));
+
+ result = plpgsql_dstring_get(&ds);
+
+ elog(NOTICE, "%s", result);
+ return result;
+ }
*** ./src/pl/plpgsql/src/plpgsql.h.orig 2006-03-26 09:52:44.000000000 +0200
--- ./src/pl/plpgsql/src/plpgsql.h 2006-03-27 20:34:09.000000000 +0200
***************
*** 424,429 ****
--- 424,430 ----
PLpgSQL_row *row;
PLpgSQL_expr *query;
List *body; /* List of statements */
+ List *params;
} PLpgSQL_stmt_dynfors;
***************
*** 446,451 ****
--- 447,453 ----
PLpgSQL_expr *argquery;
PLpgSQL_expr *query;
PLpgSQL_expr *dynquery;
+ List *params;
} PLpgSQL_stmt_open;
***************
*** 518,523 ****
--- 520,526 ----
PLpgSQL_rec *rec; /* INTO record or row variable */
PLpgSQL_row *row;
PLpgSQL_expr *query;
+ List *params; /* USING list of expressions */
} PLpgSQL_stmt_dynexecute;
*** ./src/pl/plpgsql/src/scan.l.orig 2006-03-26 09:52:25.000000000 +0200
--- ./src/pl/plpgsql/src/scan.l 2006-03-26 09:54:31.000000000 +0200
***************
*** 159,164 ****
--- 159,165 ----
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; }
