Hello
I am sending enhanced version of original patch.
2008/5/5 Tom Lane <[EMAIL PROTECTED]>:
> "Pavel Stehule" <[EMAIL PROTECTED]> writes:
>> this patch adds possibility to set additional options (SQLSTATE,
>> DETAIL, DETAIL_LOG and HINT) for RAISE statement,
>
> I looked this over briefly. A couple of comments:
>
> * Raising errors via hard-coded SQLSTATEs seems pretty unfriendly,
> at least for cases where we are reporting built-in errors. Wouldn't
> it be better to be able to raise errors using the same SQLSTATE names
> that are recognized in EXCEPTION clauses?
There are new attribut CONDITION - all defined condition are possible
without duplicit names and category conditions.
example:
RAISE NOTICE 'custom unique violation' USING (CONDITION = 'unique_violation');
>
> * If we are going to let people throw random SQLSTATEs, there had better
> be a way to name those same SQLSTATEs in EXCEPTION.
>
we can trap EXCEPTION defined via SQLSTATE now:
EXCEPTION
WHEN SQLSTATE 22001 THEN ...
> * I don't really like exposing DETAIL_LOG in this. That was a spur of
> the moment addition and we might take it out again; I think it's way
> premature to set it in stone by exposing it as a plpgsql feature.
removed
>
> * Please avoid using errstart() directly. This is unwarranted intimacy
> with elog.h's implementation and I also think it will have unpleasant
> behavior if an error occurs while evaluating the RAISE arguments.
> (In fact, I think a user could easily force a backend PANIC that way.)
> The approved way to deal with ereport options that might not be there
> is like this:
>
> ereport(ERROR,
> ( ...,
> have_sqlstate ? errcode(...) : 0,
> ...
>
> That is, you should evaluate all the options into local variables
> and then do one normal ereport call.
>
changed
> * // comments are against our coding conventions.
>
> regards, tom lane
>
removed
Regards
Pavel Stehule
*** ./doc/src/sgml/plpgsql.sgml.orig 2008-05-06 11:05:05.000000000 +0200
--- ./doc/src/sgml/plpgsql.sgml 2008-05-10 01:09:54.000000000 +0200
***************
*** 2184,2192 ****
--- 2184,2197 ----
WHEN <replaceable>condition</replaceable> <optional> OR <replaceable>condition</replaceable> ... </optional> THEN
<replaceable>handler_statements</replaceable>
<optional> WHEN <replaceable>condition</replaceable> <optional> OR <replaceable>condition</replaceable> ... </optional> THEN
+ <replaceable>handler_statements</replaceable> </optional>
+ <optional> WHEN <replaceable>SQLSTATE xxxxx</replaceable> <optional> OR <replaceable>SQLSTATE xxxxx</replaceable> ... </optional> THEN
+ <replaceable>handler_statements</replaceable> </optional>
+ <optional> WHEN <replaceable>SQLSTATE xxxxx</replaceable> <optional> OR <replaceable>condition</replaceable> ... </optional> THEN
<replaceable>handler_statements</replaceable>
... </optional>
END;
+
</synopsis>
</para>
***************
*** 2215,2221 ****
condition name <literal>OTHERS</> matches every error type except
<literal>QUERY_CANCELED</>. (It is possible, but often unwise,
to trap <literal>QUERY_CANCELED</> by name.) Condition names are
! not case-sensitive.
</para>
<para>
--- 2220,2227 ----
condition name <literal>OTHERS</> matches every error type except
<literal>QUERY_CANCELED</>. (It is possible, but often unwise,
to trap <literal>QUERY_CANCELED</> by name.) Condition names are
! not case-sensitive. Any condition can be subtituted by SQLSTATE
! value.
</para>
<para>
***************
*** 2243,2248 ****
--- 2249,2262 ----
RAISE NOTICE 'caught division_by_zero';
RETURN x;
END;
+
+ ...
+ -- or same with SQLSTATE specification
+ EXCEPTION
+ WHEN SQLSTATE 22012 THEN
+ RAISE NOTICE 'caught division_by_zero';
+ RETURN x;
+ END;
</programlisting>
When control reaches the assignment to <literal>y</>, it will
***************
*** 2832,2838 ****
raise errors.
<synopsis>
! RAISE <replaceable class="parameter">level</replaceable> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ...</optional></optional>;
</synopsis>
Possible levels are <literal>DEBUG</literal>,
--- 2846,2852 ----
raise errors.
<synopsis>
! RAISE <replaceable class="parameter">level</replaceable> '<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>;
</synopsis>
Possible levels are <literal>DEBUG</literal>,
***************
*** 2875,2891 ****
<para>
This example will abort the transaction with the given error message:
<programlisting>
! RAISE EXCEPTION 'Nonexistent ID --> %', user_id;
</programlisting>
</para>
<para>
! <command>RAISE EXCEPTION</command> presently always generates
! the same <varname>SQLSTATE</varname> code, <literal>P0001</>, no matter what message
it is invoked with. It is possible to trap this exception with
<literal>EXCEPTION ... WHEN RAISE_EXCEPTION THEN ...</> but there
is no way to tell one <command>RAISE</> from another.
</para>
</sect1>
<sect1 id="plpgsql-trigger">
--- 2889,2919 ----
<para>
This example will abort the transaction with the given error message:
<programlisting>
! RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING (hint = 'Please, check your user id');
</programlisting>
</para>
<para>
! <command>RAISE EXCEPTION</command> presently generates
! the same <varname>SQLSTATE</varname> code, <literal>P0001</> , no matter what message
it is invoked with. It is possible to trap this exception with
<literal>EXCEPTION ... WHEN RAISE_EXCEPTION THEN ...</> but there
is no way to tell one <command>RAISE</> from another.
</para>
+
+ <para>
+ With additional options is possible set some log informaition related to
+ raised exception. Possible options are <literal>SQLSTATE</literal>,
+ <literal>CONDTION</literal>, <literal>DETAIL</literal> and <literal>HINT</literal>.
+ You cannot specify <literal>SQLSTATE</literal> and <literal>CONDITION</literal>
+ together. Some conditions are not unique. These conditions cannot be used.
+ Conditional names are case sensitive. <literal>SQLSTATE</literal> categories
+ (like xx000) cannot be used too.
+ <programlisting>
+ RAISE EXCEPTION 'custom unique violation' USING (condition = 'unique_violation');
+ </programlisting>
+ </para>
+
</sect1>
<sect1 id="plpgsql-trigger">
*** ./src/pl/plpgsql/src/gram.y.orig 2008-05-06 23:54:42.000000000 +0200
--- ./src/pl/plpgsql/src/gram.y 2008-05-09 20:32:29.000000000 +0200
***************
*** 53,58 ****
--- 53,59 ----
const char *end_label);
static PLpgSQL_expr *read_cursor_args(PLpgSQL_var *cursor,
int until, const char *expected);
+ static List *read_exception_additional_options(void);
%}
***************
*** 142,147 ****
--- 143,149 ----
%type <exception_block> exception_sect
%type <exception> proc_exception
%type <condition> proc_conditions
+ %type <condition> proc_condition
%type <casewhen> case_when
%type <list> case_when_list opt_case_default
***************
*** 1305,1310 ****
--- 1307,1313 ----
new->elog_level = $3;
new->message = $4;
new->params = NIL;
+ new->options = NIL;
tok = yylex();
***************
*** 1313,1319 ****
* indicates no parameters, or a comma that
* begins the list of parameter expressions
*/
! if (tok != ',' && tok != ';')
yyerror("syntax error");
if (tok == ',')
--- 1316,1322 ----
* indicates no parameters, or a comma that
* begins the list of parameter expressions
*/
! if (tok != ',' && tok != ';' && tok != K_USING)
yyerror("syntax error");
if (tok == ',')
***************
*** 1321,1333 ****
do
{
PLpgSQL_expr *expr;
!
! expr = read_sql_expression2(',', ';',
! ", or ;",
! &tok);
new->params = lappend(new->params, expr);
} while (tok == ',');
}
$$ = (PLpgSQL_stmt *)new;
}
--- 1324,1342 ----
do
{
PLpgSQL_expr *expr;
!
! expr = read_sql_construct(',', ';', K_USING, ", or ; or USING",
! "SELECT ", true, true, &tok);
new->params = lappend(new->params, expr);
} while (tok == ',');
}
+
+ if (tok == K_USING)
+ {
+ new->options = read_exception_additional_options();
+ if (yylex() != ';')
+ yyerror("syntax error");
+ }
$$ = (PLpgSQL_stmt *)new;
}
***************
*** 1639,1657 ****
}
;
! proc_conditions : proc_conditions K_OR opt_lblname
{
PLpgSQL_condition *old;
for (old = $1; old->next != NULL; old = old->next)
/* skip */ ;
! old->next = plpgsql_parse_err_condition($3);
!
$$ = $1;
}
! | opt_lblname
{
! $$ = plpgsql_parse_err_condition($1);
}
;
--- 1648,1699 ----
}
;
! proc_conditions : proc_conditions K_OR proc_condition
{
PLpgSQL_condition *old;
for (old = $1; old->next != NULL; old = old->next)
/* skip */ ;
! old->next = $3;
$$ = $1;
}
! | proc_condition
{
! $$ = $1;
! }
! ;
!
! proc_condition : opt_lblname
! {
! $$ = plpgsql_parse_err_condition($1);
! }
! | T_SCALAR
! {
! int tok;
! PLpgSQL_condition *new;
! char buff[30];
!
! if (pg_strcasecmp(((PLpgSQL_var *) yylval.scalar)->refname, "sqlstate") != 0)
! yyerror("syntax error");
!
! /* next token is sqlstate */
! if ((tok = yylex()) == 0)
! yyerror("unexpected end of function definition");
!
! if (strlen(yytext) != 5)
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
! errmsg("sqlstate has five characters")));
!
! snprintf(buff, sizeof(buff), "sqlstate %s", yytext);
!
! new = palloc(sizeof(PLpgSQL_condition));
! new->sqlerrstate = MAKE_SQLSTATE(yytext[0], yytext[1], yytext[2],
! yytext[3], yytext[4]);
! new->condname = pstrdup(buff);
! new->next = NULL;
!
! $$ = new;
}
;
***************
*** 2805,2810 ****
--- 2847,2910 ----
}
+ /*
+ * Procedure read RAISE statements additional options
+ */
+ static List *
+ read_exception_additional_options(void)
+ {
+ List *result = NIL;
+
+ if (yylex() != '(')
+ yyerror("syntax error, expected \"(\"");
+
+ for(;;)
+ {
+ int tok;
+ PLpgSQL_raise_option *opt;
+
+ if ((tok = yylex()) == 0)
+ yyerror("unexpected end of function definition");
+
+ opt = (PLpgSQL_raise_option *) palloc(sizeof(PLpgSQL_raise_option));
+ if (pg_strcasecmp(yytext, "sqlstate") == 0)
+ {
+ opt->opt_type = PLPGSQL_RAISEOPTION_SQLSTATE;
+ }
+ else if (pg_strcasecmp(yytext, "detail") == 0)
+ {
+ opt->opt_type = PLPGSQL_RAISEOPTION_DETAIL;
+ }
+ else if (pg_strcasecmp(yytext, "condition") == 0)
+ {
+ opt->opt_type = PLPGSQL_RAISEOPTION_CONDITION;
+ }
+ else if (pg_strcasecmp(yytext, "hint") == 0)
+ {
+ opt->opt_type = PLPGSQL_RAISEOPTION_HINT;
+ }
+ else
+ {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("unknown RAISE statement option \"%s\"",
+ yytext)));
+ }
+
+ if (yylex() != K_ASSIGN)
+ yyerror("syntax error, expected \"=\"");
+
+ opt->expr = read_sql_expression2(',', ')', ", or )", &tok);
+ result = lappend(result, opt);
+
+ if (tok == ')')
+ break;
+ }
+
+ return result;
+ }
+
/* Needed to avoid conflict between different prefix settings: */
#undef yylex
*** ./src/pl/plpgsql/src/pl_comp.c.orig 2008-05-09 14:31:22.000000000 +0200
--- ./src/pl/plpgsql/src/pl_comp.c 2008-05-09 14:52:06.000000000 +0200
***************
*** 1751,1756 ****
--- 1751,1789 ----
}
/*
+ * plpgsql_identify_err_condition
+ * Check condition and translate it to SQLSTATE.
+ *
+ * Because condition names are not unique, verifies uniquness of condition
+ * name. If name isn't unique raise exception.
+ */
+ int
+ plpgsql_recognize_err_condition(char *condname)
+ {
+ int i;
+ int sqlerrstate = 0;
+ bool found = false;
+
+ for (i = 0; exception_label_map[i].label != NULL; i++)
+ if (strcmp(condname, exception_label_map[i].label) == 0)
+ {
+ if (found)
+ ereport(ERROR,
+ (errmsg("condition name doesn't identify SQLSTATE exactly")));
+
+ found = true;
+ sqlerrstate = exception_label_map[i].sqlerrstate;
+ }
+
+ if (!found)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("unrecognized exception condition \"%s\"",
+ condname)));
+ return sqlerrstate;
+ }
+
+ /*
* plpgsql_parse_err_condition
* Generate PLpgSQL_condition entry(s) for an exception condition name
*
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2008-05-06 23:09:09.000000000 +0200
--- ./src/pl/plpgsql/src/pl_exec.c 2008-05-10 07:14:49.000000000 +0200
***************
*** 2367,2372 ****
--- 2367,2375 ----
char *cp;
PLpgSQL_dstring ds;
ListCell *current_param;
+ int err_code;
+ char *err_detail = NULL;
+ char *err_hint = NULL;
plpgsql_dstring_init(&ds);
current_param = list_head(stmt->params);
***************
*** 2423,2441 ****
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("too many parameters specified for RAISE")));
/*
* Throw the error (may or may not come back)
*/
estate->err_text = raise_skip_msg; /* suppress traceback of raise */
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;
}
--- 2426,2528 ----
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("too many parameters specified for RAISE")));
+ err_code = (stmt->elog_level >= ERROR) ? ERRCODE_RAISE_EXCEPTION : 0;
+
+ if (stmt->options != NIL)
+ {
+ /* evaluate RAISE options */
+ ListCell *l;
+ bool sqlstate_changed = false;
+
+ foreach(l, stmt->options)
+ {
+ Oid optiontypeid;
+ Datum optionvalue;
+ bool optionisnull;
+ char *extval;
+
+ PLpgSQL_raise_option *opt = (PLpgSQL_raise_option *) lfirst(l);
+
+ optionvalue = exec_eval_expr(estate, opt->expr,
+ &optionisnull,
+ &optiontypeid);
+
+ if (optionisnull)
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("RAISE statement option cannot be NULL")));
+
+ extval = convert_value_to_string(optionvalue, optiontypeid);
+
+ switch (opt->opt_type)
+ {
+ case PLPGSQL_RAISEOPTION_DETAIL:
+ err_detail = pstrdup(extval);
+ break;
+ case PLPGSQL_RAISEOPTION_HINT:
+ err_hint = pstrdup(extval);
+ break;
+ case PLPGSQL_RAISEOPTION_SQLSTATE:
+ if (strlen(extval) != 5)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("sqlstate has five characters")));
+ err_code = MAKE_SQLSTATE(extval[0], extval[1], extval[2],
+ extval[3], extval[4]);
+
+ if (sqlstate_changed)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot use attributies SQLSTATE and CONDITION together")));
+
+ sqlstate_changed = true;
+ break;
+ case PLPGSQL_RAISEOPTION_CONDITION:
+ err_code = plpgsql_recognize_err_condition(extval);
+
+ if (sqlstate_changed)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot use attributies SQLSTATE and CONDITION together")));
+
+ sqlstate_changed = true;
+ break;
+
+ default:
+ elog(ERROR, "unrecognized raise option: %d", opt->opt_type);
+ }
+
+ exec_eval_cleanup(estate);
+ }
+
+ if (err_code != 0 && ERRCODE_IS_CATEGORY(err_code))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot raise category SQLSTATE value"),
+ errhint("Cannot specify SQLSTATE value like 'xx0000'.")));
+ }
+
/*
* Throw the error (may or may not come back)
*/
estate->err_text = raise_skip_msg; /* suppress traceback of raise */
ereport(stmt->elog_level,
! ((errcode(err_code),
! errmsg_internal("%s", plpgsql_dstring_get(&ds)),
! (err_detail != NULL) ? errdetail(err_detail) : 0,
! (err_hint != NULL) ? errhint(err_hint) : 0 )));
estate->err_text = NULL; /* un-suppress... */
plpgsql_dstring_free(&ds);
+ if (err_detail != NULL)
+ pfree(err_detail);
+
+ if (err_hint != NULL)
+ pfree(err_hint);
+
return PLPGSQL_RC_OK;
}
*** ./src/pl/plpgsql/src/pl_funcs.c.orig 2008-05-07 14:58:21.000000000 +0200
--- ./src/pl/plpgsql/src/pl_funcs.c 2008-05-10 01:05:42.000000000 +0200
***************
*** 1055,1061 ****
dump_expr((PLpgSQL_expr *) lfirst(lc));
printf("\n");
}
! dump_indent -= 2;
}
static void
--- 1055,1090 ----
dump_expr((PLpgSQL_expr *) lfirst(lc));
printf("\n");
}
! if (stmt->options)
! {
! dump_ind();
! printf(" USING\n");
! dump_indent += 2;
! foreach(lc, stmt->options)
! {
! PLpgSQL_raise_option *opt = (PLpgSQL_raise_option *) lfirst(lc);
!
! dump_ind();
! switch (opt->opt_type)
! {
! case PLPGSQL_RAISEOPTION_DETAIL:
! printf(" DETAIL = ");
! break;
! case PLPGSQL_RAISEOPTION_HINT:
! printf(" HINT = ");
! break;
! case PLPGSQL_RAISEOPTION_SQLSTATE:
! printf(" SQLSTATE = ");
! break;
! case PLPGSQL_RAISEOPTION_CONDITION:
! printf(" CONDITION = ");
! break;
! }
! dump_expr(opt->expr);
! printf("\n");
! }
! dump_indent -= 2;
! }
}
static void
*** ./src/pl/plpgsql/src/plpgsql.h.orig 2008-05-06 23:55:53.000000000 +0200
--- ./src/pl/plpgsql/src/plpgsql.h 2008-05-09 14:49:13.000000000 +0200
***************
*** 121,126 ****
--- 121,138 ----
};
+ /* --------
+ * RAISE statement options
+ * --------
+ */
+ enum
+ {
+ PLPGSQL_RAISEOPTION_SQLSTATE,
+ PLPGSQL_RAISEOPTION_DETAIL,
+ PLPGSQL_RAISEOPTION_HINT,
+ PLPGSQL_RAISEOPTION_CONDITION
+ };
+
/**********************************************************************
* Node and structure definitions
**********************************************************************/
***************
*** 560,567 ****
--- 572,585 ----
int elog_level;
char *message;
List *params; /* list of expressions */
+ List *options; /* elog options */
} PLpgSQL_stmt_raise;
+ typedef struct
+ { /* RAISE statement's option */
+ int opt_type;
+ PLpgSQL_expr *expr;
+ } PLpgSQL_raise_option;
typedef struct
{ /* Generic SQL statement to execute */
***************
*** 791,796 ****
--- 809,815 ----
bool add2namespace);
extern PLpgSQL_rec *plpgsql_build_record(const char *refname, int lineno,
bool add2namespace);
+ extern int plpgsql_recognize_err_condition(char *condname);
extern PLpgSQL_condition *plpgsql_parse_err_condition(char *condname);
extern void plpgsql_adddatum(PLpgSQL_datum *new);
extern int plpgsql_add_initdatums(int **varnos);
*** ./src/test/regress/expected/plpgsql.out.orig 2008-05-06 23:38:02.000000000 +0200
--- ./src/test/regress/expected/plpgsql.out 2008-05-09 23:45:39.000000000 +0200
***************
*** 3385,3387 ****
--- 3385,3508 ----
drop function case_test(int);
drop function catch();
+ -- we can't set VERBOSITY verbose
+ -- because LOCATION field contains row number from pl_exec.c
+ create or replace function run_tests()
+ returns void as $$
+ begin
+ begin
+ perform raise_options();
+ exception when others then
+ raise notice 'SQLSTATE: % SQLERRM %', sqlstate, sqlerrm;
+ end;
+ begin
+ perform raise_options2();
+ exception when others then
+ raise notice 'SQLSTATE: % SQLERRM %', sqlstate, sqlerrm;
+ end;
+ end;
+ $$ language plpgsql;
+ create or replace function raise_options() returns void as $$
+ begin
+ raise exception '% % %', 1, 2, 3
+ using (sqlstate = '55001', detail = 'some detail info', hint = 'some hint');
+ end;
+ $$ language plpgsql;
+ create or replace function raise_options2() returns void as $$
+ begin
+ raise exception 'custom unique violation'
+ using (condition = 'unique_violation', detail = 'test of unique violation', hint = 'skip it');
+ end;
+ $$ language plpgsql;
+ -- test of exception's trap
+ create or replace function raise_options3()
+ returns void as $$
+ begin
+ raise exception 'aaaa'
+ using(detail='bbb', hint=current_date, sqlstate='22201');
+ exception
+ when sqlstate 22201 then
+ raise notice 'custom 22201';
+ when unique_violation then
+ raise notice 'custom unique_violation';
+ end;
+ $$ language plpgsql;
+ create or replace function raise_options4()
+ returns void as $$
+ begin
+ raise exception 'aaaa'
+ using(detail='bbb', hint=current_date, condition='unique_violation');
+ exception
+ when sqlstate 22201 then
+ raise notice 'custom 22201';
+ when unique_violation then
+ raise notice 'custom unique_violation';
+ end;
+ $$ language plpgsql;
+ -- trap category
+ create or replace function raise_options5()
+ returns void as $$
+ begin
+ raise exception 'custom exception'
+ using(sqlstate = '33001');
+ exception
+ when sqlstate 33000 then
+ raise notice 'category: 33000 real sqlstate: %', sqlstate;
+ end;
+ $$ language plpgsql;
+ -- cannot raise category
+ create or replace function raise_options6()
+ returns void as $$
+ begin
+ -- runtime error
+ raise exception 'custom exception'
+ using(sqlstate = '33000');
+ exception
+ when sqlstate 33000 then
+ raise notice 'category: 33000 real sqlstate: %', sqlstate;
+ end;
+ $$ language plpgsql;
+ select raise_options();
+ ERROR: 1 2 3
+ DETAIL: some detail info
+ HINT: some hint
+ select run_tests();
+ NOTICE: SQLSTATE: 55001 SQLERRM 1 2 3
+ NOTICE: SQLSTATE: 23505 SQLERRM custom unique violation
+ run_tests
+ -----------
+
+ (1 row)
+
+ select raise_options3();
+ NOTICE: custom 22201
+ raise_options3
+ ----------------
+
+ (1 row)
+
+ select raise_options4();
+ NOTICE: custom unique_violation
+ raise_options4
+ ----------------
+
+ (1 row)
+
+ select raise_options5();
+ NOTICE: category: 33000 real sqlstate: 33001
+ raise_options5
+ ----------------
+
+ (1 row)
+
+ select raise_options6();
+ ERROR: cannot raise category SQLSTATE value
+ HINT: Cannot specify SQLSTATE value like 'xx0000'.
+ CONTEXT: PL/pgSQL function "raise_options6" line 3 at RAISE
+ drop function raise_options();
+ drop function raise_options2();
+ drop function raise_options3();
+ drop function raise_options4();
+ drop function raise_options5();
+ drop function raise_options6();
+ drop function run_tests();
*** ./src/test/regress/sql/plpgsql.sql.orig 2008-05-06 23:36:47.000000000 +0200
--- ./src/test/regress/sql/plpgsql.sql 2008-05-09 23:26:26.000000000 +0200
***************
*** 2735,2737 ****
--- 2735,2836 ----
drop function case_test(int);
drop function catch();
+ -- we can't set VERBOSITY verbose
+ -- because LOCATION field contains row number from pl_exec.c
+ create or replace function run_tests()
+ returns void as $$
+ begin
+ begin
+ perform raise_options();
+ exception when others then
+ raise notice 'SQLSTATE: % SQLERRM %', sqlstate, sqlerrm;
+ end;
+ begin
+ perform raise_options2();
+ exception when others then
+ raise notice 'SQLSTATE: % SQLERRM %', sqlstate, sqlerrm;
+ end;
+ end;
+ $$ language plpgsql;
+
+ create or replace function raise_options() returns void as $$
+ begin
+ raise exception '% % %', 1, 2, 3
+ using (sqlstate = '55001', detail = 'some detail info', hint = 'some hint');
+ end;
+ $$ language plpgsql;
+
+ create or replace function raise_options2() returns void as $$
+ begin
+ raise exception 'custom unique violation'
+ using (condition = 'unique_violation', detail = 'test of unique violation', hint = 'skip it');
+ end;
+ $$ language plpgsql;
+
+ -- test of exception's trap
+ create or replace function raise_options3()
+ returns void as $$
+ begin
+ raise exception 'aaaa'
+ using(detail='bbb', hint=current_date, sqlstate='22201');
+ exception
+ when sqlstate 22201 then
+ raise notice 'custom 22201';
+ when unique_violation then
+ raise notice 'custom unique_violation';
+ end;
+ $$ language plpgsql;
+
+ create or replace function raise_options4()
+ returns void as $$
+ begin
+ raise exception 'aaaa'
+ using(detail='bbb', hint=current_date, condition='unique_violation');
+ exception
+ when sqlstate 22201 then
+ raise notice 'custom 22201';
+ when unique_violation then
+ raise notice 'custom unique_violation';
+ end;
+ $$ language plpgsql;
+
+ -- trap category
+ create or replace function raise_options5()
+ returns void as $$
+ begin
+ raise exception 'custom exception'
+ using(sqlstate = '33001');
+ exception
+ when sqlstate 33000 then
+ raise notice 'category: 33000 real sqlstate: %', sqlstate;
+ end;
+ $$ language plpgsql;
+
+ -- cannot raise category
+ create or replace function raise_options6()
+ returns void as $$
+ begin
+ -- runtime error
+ raise exception 'custom exception'
+ using(sqlstate = '33000');
+ exception
+ when sqlstate 33000 then
+ raise notice 'category: 33000 real sqlstate: %', sqlstate;
+ end;
+ $$ language plpgsql;
+
+
+ select raise_options();
+ select run_tests();
+ select raise_options3();
+ select raise_options4();
+ select raise_options5();
+ select raise_options6();
+
+ drop function raise_options();
+ drop function raise_options2();
+ drop function raise_options3();
+ drop function raise_options4();
+ drop function raise_options5();
+ drop function raise_options6();
+ drop function run_tests();
--
Sent via pgsql-patches mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
