rebased patch Regards
Pavel 2013/11/14 Peter Eisentraut <pete...@gmx.net> > On Wed, 2013-10-09 at 18:57 +0200, Pavel Stehule wrote: > > here is a patch for RAISE WHEN clause > > Your patch needs to be rebased. > >
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index ca2c2b5..d6845d7 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1753,9 +1753,7 @@ BEGIN -- Since execution is not finished, we can check whether rows were returned -- and raise exception if not. - IF NOT FOUND THEN - RAISE EXCEPTION 'No flight at %.', $1; - END IF; + RAISE EXCEPTION 'No flight at %.', $1 WHEN NOT FOUND; RETURN; END @@ -3376,11 +3374,11 @@ END LOOP <optional> <replaceable>label</replaceable> </optional>; raise errors. <synopsis> -RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ... </optional></optional> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>; -RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> <replaceable class="parameter">condition_name</> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>; -RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> SQLSTATE '<replaceable class="parameter">sqlstate</>' <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional>; -RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional>; -RAISE ; +RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ... </optional></optional> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional> <optional> WHEN <replaceable>boolean-expression</replaceable> </optional> ; +RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> <replaceable class="parameter">condition_name</> <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional> <optional> WHEN <replaceable>boolean-expression</replaceable> </optional>; +RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> SQLSTATE '<replaceable class="parameter">sqlstate</>' <optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> </optional> <optional> WHEN <replaceable>boolean-expression</replaceable> </optional>; +RAISE <optional> <replaceable class="parameter">level</replaceable> </optional> USING <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> <optional> WHEN <replaceable>boolean-expression</replaceable> </optional>; +RAISE <optional> WHEN <replaceable>boolean-expression</replaceable> </optional> ; </synopsis> The <replaceable class="parameter">level</replaceable> option specifies diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index bc31fe9..edb6105 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -2874,6 +2874,20 @@ exec_stmt_raise(PLpgSQL_execstate *estate, PLpgSQL_stmt_raise *stmt) char *err_schema = NULL; ListCell *lc; + /* check condition when is entered */ + if (stmt->cond != NULL) + { + bool value; + bool isnull; + + value = exec_eval_boolean(estate, stmt->cond, &isnull); + exec_eval_cleanup(estate); + + /* ignore statement, when result of condition is false or NULL */ + if (isnull || value == false) + return PLPGSQL_RC_OK; + } + /* RAISE with no parameters: re-throw current exception */ if (stmt->condname == NULL && stmt->message == NULL && stmt->options == NIL) diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y index f112282..a4d7035 100644 --- a/src/pl/plpgsql/src/pl_gram.y +++ b/src/pl/plpgsql/src/pl_gram.y @@ -63,6 +63,7 @@ static void current_token_is_not_variable(int tok); static PLpgSQL_expr *read_sql_construct(int until, int until2, int until3, + int until4, const char *expected, const char *sqlstart, bool isexpression, @@ -105,7 +106,7 @@ static void check_labels(const char *start_label, int end_location); static PLpgSQL_expr *read_cursor_args(PLpgSQL_var *cursor, int until, const char *expected); -static List *read_raise_options(void); +static List *read_raise_options(int *tok); %} @@ -1386,6 +1387,7 @@ for_control : for_variable K_IN expr1 = read_sql_construct(DOT_DOT, K_LOOP, 0, + 0, "LOOP", "SELECT ", true, @@ -1690,6 +1692,7 @@ stmt_raise : K_RAISE new->message = NULL; new->params = NIL; new->options = NIL; + new->cond = NULL; tok = yylex(); if (tok == 0) @@ -1760,22 +1763,22 @@ stmt_raise : K_RAISE * or USING to begin the options list. */ tok = yylex(); - if (tok != ',' && tok != ';' && tok != K_USING) + if (tok != ',' && tok != ';' && tok != K_USING && tok != K_WHEN) yyerror("syntax error"); while (tok == ',') { PLpgSQL_expr *expr; - expr = read_sql_construct(',', ';', K_USING, - ", or ; or USING", + expr = read_sql_construct(',', ';', K_USING, K_WHEN, + ", or ; or USING or WHEN", "SELECT ", true, true, true, NULL, &tok); new->params = lappend(new->params, expr); } } - else if (tok != K_USING) + else if (tok != K_USING && tok != K_WHEN) { /* must be condition name or SQLSTATE */ if (tok_is_keyword(tok, &yylval, @@ -1806,12 +1809,15 @@ stmt_raise : K_RAISE false); } tok = yylex(); - if (tok != ';' && tok != K_USING) + if (tok != ';' && tok != K_USING && tok != K_WHEN) yyerror("syntax error"); } if (tok == K_USING) - new->options = read_raise_options(); + new->options = read_raise_options(&tok); + + if (tok == K_WHEN) + new->cond = read_sql_expression(';', ";"); } $$ = (PLpgSQL_stmt *)new; @@ -1868,7 +1874,7 @@ stmt_dynexecute : K_EXECUTE PLpgSQL_expr *expr; int endtoken; - expr = read_sql_construct(K_INTO, K_USING, ';', + expr = read_sql_construct(K_INTO, K_USING, ';', 0, "INTO or USING or ;", "SELECT ", true, true, true, @@ -1907,7 +1913,7 @@ stmt_dynexecute : K_EXECUTE yyerror("syntax error"); do { - expr = read_sql_construct(',', ';', K_INTO, + expr = read_sql_construct(',', ';', K_INTO, 0, ", or ; or INTO", "SELECT ", true, true, true, @@ -2418,7 +2424,7 @@ current_token_is_not_variable(int tok) static PLpgSQL_expr * read_sql_expression(int until, const char *expected) { - return read_sql_construct(until, 0, 0, expected, + return read_sql_construct(until, 0, 0, 0, expected, "SELECT ", true, true, true, NULL, NULL); } @@ -2427,7 +2433,7 @@ static PLpgSQL_expr * read_sql_expression2(int until, int until2, const char *expected, int *endtoken) { - return read_sql_construct(until, until2, 0, expected, + return read_sql_construct(until, until2, 0, 0, expected, "SELECT ", true, true, true, NULL, endtoken); } @@ -2435,7 +2441,7 @@ read_sql_expression2(int until, int until2, const char *expected, static PLpgSQL_expr * read_sql_stmt(const char *sqlstart) { - return read_sql_construct(';', 0, 0, ";", + return read_sql_construct(';', 0, 0, 0, ";", sqlstart, false, true, true, NULL, NULL); } @@ -2458,6 +2464,7 @@ static PLpgSQL_expr * read_sql_construct(int until, int until2, int until3, + int until4, const char *expected, const char *sqlstart, bool isexpression, @@ -2491,6 +2498,8 @@ read_sql_construct(int until, break; if (tok == until3 && parenlevel == 0) break; + if (tok == until4 && parenlevel == 0) + break; if (tok == '(' || tok == '[') parenlevel++; else if (tok == ')' || tok == ']') @@ -3609,7 +3618,7 @@ read_cursor_args(PLpgSQL_var *cursor, int until, const char *expected) * translated into a form where the second parameter is commented * out. */ - item = read_sql_construct(',', ')', 0, + item = read_sql_construct(',', ')', 0, 0, ",\" or \")", sqlstart, true, true, @@ -3673,59 +3682,59 @@ read_cursor_args(PLpgSQL_var *cursor, int until, const char *expected) * Parse RAISE ... USING options */ static List * -read_raise_options(void) +read_raise_options(int *tok) { List *result = NIL; for (;;) { PLpgSQL_raise_option *opt; - int tok; - if ((tok = yylex()) == 0) + if ((*tok = yylex()) == 0) yyerror("unexpected end of function definition"); opt = (PLpgSQL_raise_option *) palloc(sizeof(PLpgSQL_raise_option)); - if (tok_is_keyword(tok, &yylval, + if (tok_is_keyword(*tok, &yylval, K_ERRCODE, "errcode")) opt->opt_type = PLPGSQL_RAISEOPTION_ERRCODE; - else if (tok_is_keyword(tok, &yylval, + else if (tok_is_keyword(*tok, &yylval, K_MESSAGE, "message")) opt->opt_type = PLPGSQL_RAISEOPTION_MESSAGE; - else if (tok_is_keyword(tok, &yylval, + else if (tok_is_keyword(*tok, &yylval, K_DETAIL, "detail")) opt->opt_type = PLPGSQL_RAISEOPTION_DETAIL; - else if (tok_is_keyword(tok, &yylval, + else if (tok_is_keyword(*tok, &yylval, K_HINT, "hint")) opt->opt_type = PLPGSQL_RAISEOPTION_HINT; - else if (tok_is_keyword(tok, &yylval, + else if (tok_is_keyword(*tok, &yylval, K_COLUMN, "column")) opt->opt_type = PLPGSQL_RAISEOPTION_COLUMN; - else if (tok_is_keyword(tok, &yylval, + else if (tok_is_keyword(*tok, &yylval, K_CONSTRAINT, "constraint")) opt->opt_type = PLPGSQL_RAISEOPTION_CONSTRAINT; - else if (tok_is_keyword(tok, &yylval, + else if (tok_is_keyword(*tok, &yylval, K_DATATYPE, "datatype")) opt->opt_type = PLPGSQL_RAISEOPTION_DATATYPE; - else if (tok_is_keyword(tok, &yylval, + else if (tok_is_keyword(*tok, &yylval, K_TABLE, "table")) opt->opt_type = PLPGSQL_RAISEOPTION_TABLE; - else if (tok_is_keyword(tok, &yylval, + else if (tok_is_keyword(*tok, &yylval, K_SCHEMA, "schema")) opt->opt_type = PLPGSQL_RAISEOPTION_SCHEMA; else yyerror("unrecognized RAISE statement option"); - tok = yylex(); - if (tok != '=' && tok != COLON_EQUALS) + *tok = yylex(); + if (*tok != '=' && *tok != COLON_EQUALS) yyerror("syntax error, expected \"=\""); - opt->expr = read_sql_expression2(',', ';', ", or ;", &tok); + opt->expr = read_sql_construct(',', ';', K_WHEN, 0, ", or ; or WHEN", + "SELECT ", true, true, true, NULL, tok); result = lappend(result, opt); - if (tok == ';') + if (*tok == ';' || *tok == K_WHEN) break; } diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h index 9cb4f53..6b39f81 100644 --- a/src/pl/plpgsql/src/plpgsql.h +++ b/src/pl/plpgsql/src/plpgsql.h @@ -621,6 +621,7 @@ typedef struct char *message; /* old-style message format literal, or NULL */ List *params; /* list of expressions for old-style message */ List *options; /* list of PLpgSQL_raise_option */ + PLpgSQL_expr *cond; /* a boolean expression if it is used */ } PLpgSQL_stmt_raise; typedef struct diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index 22c16c4..e861bda 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -5189,3 +5189,42 @@ NOTICE: outer_func() done drop function outer_outer_func(int); drop function outer_func(int); drop function inner_func(int); +-- test of conditional raise statement +create or replace function raise_test1(int) +returns void as $$ +begin + raise notice 'hello' when $1 > 10; + raise notice '% % %', 1, $1, 'hello' when $1 > 10; + raise notice 'hello, %', upper('world') using detail='bla bla bla' when $1 > 10; +end; +$$ language plpgsql; +ERROR: cannot change return type of existing function +HINT: Use DROP FUNCTION raise_test1(integer) first. +select raise_test1(0); +ERROR: too many parameters specified for RAISE +CONTEXT: PL/pgSQL function raise_test1(integer) line 3 at RAISE +select raise_test1(20); +ERROR: too many parameters specified for RAISE +CONTEXT: PL/pgSQL function raise_test1(integer) line 3 at RAISE +drop function raise_test1(int); +create table plpgsql_target_table(a int); +insert into plpgsql_target_table values(10); +create or replace function raise_test2(int) +returns int as $$ +declare _a int; +begin + select into _a a from plpgsql_target_table where a = $1; + raise exception 'there are no row where a = %', $1 when not found; + return _a; +end; +$$ language plpgsql; +select raise_test2(1); +ERROR: there are no row where a = 1 +select raise_test2(10); + raise_test2 +------------- + 10 +(1 row) + +drop function raise_test2(int); +drop table plpgsql_target_table; diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index a685fa2..81b46f7 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -4084,3 +4084,37 @@ select outer_outer_func(20); drop function outer_outer_func(int); drop function outer_func(int); drop function inner_func(int); + +-- test of conditional raise statement +create or replace function raise_test1(int) +returns void as $$ +begin + raise notice 'hello' when $1 > 10; + raise notice '% % %', 1, $1, 'hello' when $1 > 10; + raise notice 'hello, %', upper('world') using detail='bla bla bla' when $1 > 10; +end; +$$ language plpgsql; + +select raise_test1(0); +select raise_test1(20); + +drop function raise_test1(int); + +create table plpgsql_target_table(a int); +insert into plpgsql_target_table values(10); + +create or replace function raise_test2(int) +returns int as $$ +declare _a int; +begin + select into _a a from plpgsql_target_table where a = $1; + raise exception 'there are no row where a = %', $1 when not found; + return _a; +end; +$$ language plpgsql; + +select raise_test2(1); +select raise_test2(10); + +drop function raise_test2(int); +drop table plpgsql_target_table;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers