Hello this patch adds possibility to set additional options (SQLSTATE, DETAIL, DETAIL_LOG and HINT) for RAISE statement,
Proposal: http://archives.postgresql.org/pgsql-hackers/2008-04/msg00919.php I changed keyword from WITH to USING, because I don't would to create new keyword RAISE level 'format' [, expression [, ...]] [ USING ( option = expression [, ... ] ) ]; RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING (hint = 'Please, check your user id'); Regards Pavel Stehule
*** ./doc/src/sgml/plpgsql.sgml.orig 2008-04-16 11:17:51.000000000 +0200 --- ./doc/src/sgml/plpgsql.sgml 2008-04-16 12:59:44.000000000 +0200 *************** *** 2742,2748 **** 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>, --- 2742,2748 ---- 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>, *************** *** 2785,2801 **** <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"> --- 2785,2808 ---- <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>DETAIL</literal>, <literal>DETAIL_LOG</literal> and <literal>HINT</literal>. + </para> + </sect1> <sect1 id="plpgsql-trigger"> *** ./src/pl/plpgsql/src/gram.y.orig 2008-04-15 07:37:03.000000000 +0200 --- ./src/pl/plpgsql/src/gram.y 2008-04-15 13:42:36.000000000 +0200 *************** *** 52,57 **** --- 52,58 ---- const char *end_label); static PLpgSQL_expr *read_cursor_args(PLpgSQL_var *cursor, int until, const char *expected); + static List *read_exception_additional_options(); %} *************** *** 1258,1263 **** --- 1259,1265 ---- new->elog_level = $3; new->message = $4; new->params = NIL; + new->options = NIL; tok = yylex(); *************** *** 1266,1272 **** * indicates no parameters, or a comma that * begins the list of parameter expressions */ ! if (tok != ',' && tok != ';') yyerror("syntax error"); if (tok == ',') --- 1268,1274 ---- * indicates no parameters, or a comma that * begins the list of parameter expressions */ ! if (tok != ',' && tok != ';' && tok != K_USING) yyerror("syntax error"); if (tok == ',') *************** *** 1274,1286 **** do { PLpgSQL_expr *expr; ! ! expr = read_sql_expression2(',', ';', ! ", or ;", ! &tok); new->params = lappend(new->params, expr); } while (tok == ','); } $$ = (PLpgSQL_stmt *)new; } --- 1276,1294 ---- 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; } *************** *** 2633,2638 **** --- 2641,2704 ---- } + /* + * Procedure read RAISE statements additional options + */ + static List * + read_exception_additional_options() + { + 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, "detail_log") == 0) + { + opt->opt_type = PLPGSQL_RAISEOPTION_DETAIL_LOG; + } + 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_exec.c.orig 2008-04-15 11:40:42.000000000 +0200 --- ./src/pl/plpgsql/src/pl_exec.c 2008-04-15 13:37:38.000000000 +0200 *************** *** 2318,2327 **** */ 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); --- 2318,2385 ---- */ estate->err_text = raise_skip_msg; /* suppress traceback of raise */ ! if (stmt->options != NIL) ! { ! /* evaluate RAISE options */ ! ListCell *l; ! ! errstart(stmt->elog_level, __FILE__, __LINE__, PG_FUNCNAME_MACRO); ! ! errmsg_internal("%s", plpgsql_dstring_get(&ds)); ! ! 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 ne NULL"))); + + extval = convert_value_to_string(optionvalue, optiontypeid); + + //exec_eval_cleanup(estate); + + switch (opt->opt_type) + { + case PLPGSQL_RAISEOPTION_DETAIL_LOG: + errdetail_log(extval); + break; + case PLPGSQL_RAISEOPTION_DETAIL: + errdetail(extval); + break; + case PLPGSQL_RAISEOPTION_HINT: + errhint(extval); + break; + case PLPGSQL_RAISEOPTION_SQLSTATE: + if (strlen(extval) != 5) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("sqlstate has five characters"))); + errcode(MAKE_SQLSTATE(extval[0], extval[1], extval[2], + extval[3], extval[4])); + break; + } + } + + errfinish(0); + + } + else + { + 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); *** ./src/pl/plpgsql/src/plpgsql.h.orig 2008-04-15 08:23:46.000000000 +0200 --- ./src/pl/plpgsql/src/plpgsql.h 2008-04-15 12:42:02.000000000 +0200 *************** *** 120,125 **** --- 120,137 ---- }; + /* -------- + * RAISE statement options + * -------- + */ + enum + { + PLPGSQL_RAISEOPTION_SQLSTATE, + PLPGSQL_RAISEOPTION_DETAIL, + PLPGSQL_RAISEOPTION_DETAIL_LOG, + PLPGSQL_RAISEOPTION_HINT + }; + /********************************************************************** * Node and structure definitions **********************************************************************/ *************** *** 539,546 **** --- 551,564 ---- 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 */ *** ./src/test/regress/expected/plpgsql.out.orig 2008-04-16 13:23:12.000000000 +0200 --- ./src/test/regress/expected/plpgsql.out 2008-04-16 13:19:27.000000000 +0200 *************** *** 3267,3269 **** --- 3267,3281 ---- $$ language plpgsql; ERROR: cursor FOR loop must use a bound cursor variable CONTEXT: compile of PL/pgSQL function "forc_bad" near line 4 + -- specified sqlstate is invisible, we can't set VERBOSITY verbose + -- because LOCATION field contains row number from pl_exec.c + 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; + select raise_options(); + ERROR: 1 2 3 + DETAIL: some detail info + HINT: some hint + drop function raise_options(); *** ./src/test/regress/sql/plpgsql.sql.orig 2008-04-16 13:03:14.000000000 +0200 --- ./src/test/regress/sql/plpgsql.sql 2008-04-16 13:18:53.000000000 +0200 *************** *** 2669,2671 **** --- 2669,2683 ---- end loop; end; $$ language plpgsql; + + -- specified sqlstate is invisible, we can't set VERBOSITY verbose + -- because LOCATION field contains row number from pl_exec.c + 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; + + select raise_options(); + drop function raise_options(); +
-- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches