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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
