rebased patch
Regards
Pavel
2013/11/14 Peter Eisentraut <[email protected]>
> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
