*** ./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 scalar variables"); } 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 = ""; ! 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 "; + 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; }