From d6636543c4becc4ba9989af8e5b490e1ee2e7c0e Mon Sep 17 00:00:00 2001 From: Neil Conway Date: Tue, 14 Jun 2005 06:43:15 +0000 Subject: [PATCH] Allow the parameters to PL/PgSQL's RAISE statement to be expressions, instead of just scalar variables. Add regression tests and update the documentation. Along the way, remove some redundant error checking code from exec_stmt_perform(). Original patch from Pavel Stehule, reworked by Neil Conway. --- doc/src/sgml/plpgsql.sgml | 8 ++-- src/pl/plpgsql/src/gram.y | 63 +++++++++++++-------------- src/pl/plpgsql/src/pl_exec.c | 28 ++++-------- src/pl/plpgsql/src/pl_funcs.c | 22 +++++++--- src/pl/plpgsql/src/plpgsql.h | 4 +- src/test/regress/expected/plpgsql.out | 54 ++++++++++++++++------- src/test/regress/sql/plpgsql.sql | 34 +++++++++++---- 7 files changed, 124 insertions(+), 89 deletions(-) diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index c043c78fea..1389727527 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,5 +1,5 @@ @@ -2533,9 +2533,9 @@ RAISE level 'cmd_type = PLPGSQL_STMT_RAISE; - new->lineno = $2; - new->elog_level = $3; - new->message = $4; - new->params = NIL; + if (tok == ',') + { + PLpgSQL_expr *expr; + int term; + + for (;;) + { + expr = read_sql_construct(',', ';', ", or ;", + "SELECT ", + true, true, &term); + new->params = lappend(new->params, expr); + if (term == ';') + break; + } + } $$ = (PLpgSQL_stmt *)new; } @@ -1219,22 +1232,6 @@ raise_level : K_EXCEPTION } ; -raise_params : raise_params raise_param - { - $$ = lappend_int($1, $2); - } - | raise_param - { - $$ = list_make1_int($1); - } - ; - -raise_param : ',' T_SCALAR - { - $$ = yylval.scalar->dno; - } - ; - loop_body : proc_sect K_END K_LOOP ';' { $$ = $1; } ; @@ -1658,7 +1655,7 @@ read_sql_stmt(const char *sqlstart) * 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" - * valid_sql: whether to check the syntax of the expression (plus sqlstart) + * valid_sql: whether to check the syntax of the expr (prefixed with sqlstart) * endtoken: if not NULL, ending token is stored at *endtoken * (this is only interesting if until2 isn't zero) */ diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index ae5f747314..a2a1f7cb6b 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -3,7 +3,7 @@ * procedural language * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.143 2005/06/10 16:23:11 neilc Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.144 2005/06/14 06:43:14 neilc Exp $ * * This software is copyrighted by Jan Wieck - Hamburg. * @@ -594,7 +594,7 @@ plpgsql_exec_trigger(PLpgSQL_function *func, error_context_stack = plerrcontext.previous; /* - * Return the triggers result + * Return the trigger's result */ return rettup; } @@ -1095,22 +1095,9 @@ static int exec_stmt_perform(PLpgSQL_execstate *estate, PLpgSQL_stmt_perform *stmt) { PLpgSQL_expr *expr = stmt->expr; - int rc; - - /* - * If not already done create a plan for this expression - */ - if (expr->plan == NULL) - exec_prepare_plan(estate, expr); - - rc = exec_run_select(estate, expr, 0, NULL); - if (rc != SPI_OK_SELECT) - ereport(ERROR, - (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("query \"%s\" did not return data", expr->query))); + (void) exec_run_select(estate, expr, 0, NULL); exec_set_found(estate, (estate->eval_processed != 0)); - exec_eval_cleanup(estate); return PLPGSQL_RC_OK; @@ -1941,15 +1928,18 @@ exec_stmt_raise(PLpgSQL_execstate *estate, PLpgSQL_stmt_raise *stmt) (errcode(ERRCODE_SYNTAX_ERROR), errmsg("too few parameters specified for RAISE"))); - exec_eval_datum(estate, estate->datums[lfirst_int(current_param)], - InvalidOid, - ¶mtypeid, ¶mvalue, ¶misnull); + 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; } diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c index 21d8d9b7f7..553eef7c49 100644 --- a/src/pl/plpgsql/src/pl_funcs.c +++ b/src/pl/plpgsql/src/pl_funcs.c @@ -3,7 +3,7 @@ * procedural language * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.42 2005/06/14 00:10:02 neilc Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.43 2005/06/14 06:43:14 neilc Exp $ * * This software is copyrighted by Jan Wieck - Hamburg. * @@ -885,13 +885,20 @@ dump_return_next(PLpgSQL_stmt_return_next *stmt) static void dump_raise(PLpgSQL_stmt_raise *stmt) { - ListCell *l; + ListCell *lc; + int i = 0; dump_ind(); - printf("RAISE '%s'", stmt->message); - foreach (l, stmt->params) - printf(" %d", lfirst_int(l)); - printf("\n"); + printf("RAISE '%s'\n", stmt->message); + dump_indent += 2; + foreach (lc, stmt->params) + { + dump_ind(); + printf(" parameter %d: ", i++); + dump_expr((PLpgSQL_expr *) lfirst(lc)); + printf("\n"); + } + dump_indent -= 2; } static void @@ -916,7 +923,8 @@ dump_dynexecute(PLpgSQL_stmt_dynexecute *stmt) { dump_ind(); printf(" target = %d %s\n", stmt->rec->recno, stmt->rec->refname); - } else if (stmt->row != NULL) + } + else if (stmt->row != NULL) { dump_ind(); printf(" target = %d %s\n", stmt->row->rowno, stmt->row->refname); diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h index 580439c88c..a724df6796 100644 --- a/src/pl/plpgsql/src/plpgsql.h +++ b/src/pl/plpgsql/src/plpgsql.h @@ -3,7 +3,7 @@ * procedural language * * IDENTIFICATION - * $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.62 2005/06/10 16:23:11 neilc Exp $ + * $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.63 2005/06/14 06:43:14 neilc Exp $ * * This software is copyrighted by Jan Wieck - Hamburg. * @@ -515,7 +515,7 @@ typedef struct int lineno; int elog_level; char *message; - List *params; + List *params; /* list of expressions */ } PLpgSQL_stmt_raise; diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index 2650cbb908..21101958ab 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -2418,17 +2418,17 @@ drop type eitype cascade; -- -- SQLSTATE and SQLERRM test -- --- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION --- blocks -create function excpt_test() returns void as $$ +create function excpt_test1() returns void as $$ begin raise notice '% %', sqlstate, sqlerrm; end; $$ language plpgsql; -ERROR: syntax error at or near "sqlstate" at character 79 -LINE 3: raise notice '% %', sqlstate, sqlerrm; - ^ --- should fail -create function excpt_test() returns void as $$ +-- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION +-- blocks +select excpt_test1(); +ERROR: column "sqlstate" does not exist +CONTEXT: SQL statement "SELECT sqlstate" +PL/pgSQL function "excpt_test1" line 2 at raise +create function excpt_test2() returns void as $$ begin begin begin @@ -2436,10 +2436,12 @@ begin end; end; end; $$ language plpgsql; -ERROR: syntax error at or near "sqlstate" at character 108 -LINE 5: raise notice '% %', sqlstate, sqlerrm; - ^ -create function excpt_test() returns void as $$ +-- should fail +select excpt_test2(); +ERROR: column "sqlstate" does not exist +CONTEXT: SQL statement "SELECT sqlstate" +PL/pgSQL function "excpt_test2" line 4 at raise +create function excpt_test3() returns void as $$ begin begin raise exception 'user exception'; @@ -2458,14 +2460,34 @@ begin raise notice '% %', sqlstate, sqlerrm; end; end; $$ language plpgsql; -select excpt_test(); +select excpt_test3(); NOTICE: caught exception P0001 user exception NOTICE: P0001 user exception NOTICE: caught exception 22012 division by zero NOTICE: P0001 user exception - excpt_test ------------- + excpt_test3 +------------- + +(1 row) + +drop function excpt_test1(); +drop function excpt_test2(); +drop function excpt_test3(); +-- parameters of raise stmt can be expressions +create function raise_exprs() returns void as $$ +declare + a integer[] = '{10,20,30}'; + c varchar = 'xyz'; + i integer; +begin + i := 2; + raise notice '%; %; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',NULL,30), NULL; +end;$$ language plpgsql; +select raise_exprs(); +NOTICE: {10,20,30}; 20; xyz; xyzabc; (10,aaa,,30); + raise_exprs +------------- (1 row) -drop function excpt_test(); +drop function raise_exprs(); diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index 9dc00f2f1e..375eef8959 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -2055,15 +2055,15 @@ drop type eitype cascade; -- SQLSTATE and SQLERRM test -- --- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION --- blocks -create function excpt_test() returns void as $$ +create function excpt_test1() returns void as $$ begin raise notice '% %', sqlstate, sqlerrm; end; $$ language plpgsql; +-- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION +-- blocks +select excpt_test1(); --- should fail -create function excpt_test() returns void as $$ +create function excpt_test2() returns void as $$ begin begin begin @@ -2071,8 +2071,10 @@ begin end; end; end; $$ language plpgsql; +-- should fail +select excpt_test2(); -create function excpt_test() returns void as $$ +create function excpt_test3() returns void as $$ begin begin raise exception 'user exception'; @@ -2092,5 +2094,21 @@ begin end; end; $$ language plpgsql; -select excpt_test(); -drop function excpt_test(); +select excpt_test3(); +drop function excpt_test1(); +drop function excpt_test2(); +drop function excpt_test3(); + +-- parameters of raise stmt can be expressions +create function raise_exprs() returns void as $$ +declare + a integer[] = '{10,20,30}'; + c varchar = 'xyz'; + i integer; +begin + i := 2; + raise notice '%; %; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',NULL,30), NULL; +end;$$ language plpgsql; + +select raise_exprs(); +drop function raise_exprs(); -- 2.40.0