patch: enhanced get diagnostics statement 2
Started by Pavel Stehuleover 14 years ago10 messages
Hello
This patch enhances a GET DIAGNOSTICS statement functionality. It adds
a possibility of access to exception's data. These data are stored on
stack when exception's handler is activated - and these data are
access-able everywhere inside handler. It has a different behave (the
content is immutable inside handler) and therefore it has modified
syntax (use keyword STACKED). This implementation is in conformance
with ANSI SQL and SQL/PSM - implemented two standard fields -
RETURNED_SQLSTATE and MESSAGE_TEXT and three PostgreSQL specific
fields - PG_EXCEPTION_DETAIL, PG_EXCEPTION_HINT and
PG_EXCEPTION_CONTEXT.
The GET STACKED DIAGNOSTICS statement is allowed only inside
exception's handler. When it is used outside handler, then diagnostics
exception 0Z002 is raised.
This patch has no impact on performance. It is just interface to
existing stacked 'edata' structure. This patch doesn't change a
current behave of GET DIAGNOSTICS statement.
CREATE OR REPLACE FUNCTION public.stacked_diagnostics_test02()
RETURNS void
LANGUAGE plpgsql
AS $function$
declare _detail text; _hint text; _message text;
begin
perform ...
exception when others then
get stacked diagnostics
_message = message_text,
_detail = pg_exception_detail,
_hint = pg_exception_hint;
raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
end;
$function$
All regress tests was passed.
Regards
Pavel Stehule
Attachments:
getdiag.difftext/x-patch; charset=US-ASCII; name=getdiag.diffDownload
*** ./doc/src/sgml/plpgsql.sgml.orig 2011-05-18 14:22:28.000000000 +0200
--- ./doc/src/sgml/plpgsql.sgml 2011-06-02 09:43:49.682013158 +0200
***************
*** 1387,1393 ****
command, which has the form:
<synopsis>
! GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
</synopsis>
This command allows retrieval of system status indicators. Each
--- 1387,1393 ----
command, which has the form:
<synopsis>
! GET <optional> CURRENT | STACKED </optional> DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
</synopsis>
This command allows retrieval of system status indicators. Each
***************
*** 1486,1491 ****
--- 1486,1516 ----
affect only the current function.
</para>
+ <para>
+ Inside a exception handler is possible to use a stacked diagnostics statement. It
+ allows a access to exception's data: the <varname>RETURNED_SQLSTATE</varname> contains
+ a SQLSTATE of handled exception. <varname>MESSAGE_TEXT</varname> contains a message text,
+ <varname>PG_EXCEPTION_DETAIL</varname> has a text that is shown as exception detail,
+ <varname>PG_EXCEPTION_HINT</varname> has a hint related to catched exception.
+ <varname>PG_EXCEPTION_CONTEXT</varname> contains a lines that describes call stack. These
+ variables holds a text value. When some field of exception are not filled, then related
+ variable contains a empty string,
+ </para>
+
+ <para>
+ An example:
+ <programlisting>
+ BEGIN
+ ...
+ EXCEPTION WHEN OTHERS THEN
+ GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
+ text_var2 = PG_EXCEPTION_DETAIL,
+ text_var3 = PG_EXCEPTION_HINT;
+ END;
+ </programlisting>
+ </para>
+
+
</sect2>
<sect2 id="plpgsql-statements-null">
*** ./src/backend/utils/errcodes.txt.orig 2011-05-18 14:22:29.000000000 +0200
--- ./src/backend/utils/errcodes.txt 2011-06-01 20:43:16.128831780 +0200
***************
*** 132,137 ****
--- 132,141 ----
0P000 E ERRCODE_INVALID_ROLE_SPECIFICATION invalid_role_specification
+ Section: Class 0Z - Diagnostics Exception
+ 0Z000 E ERRCODE_DIAGNOSTICS_EXCEPTION diagnostics_exception
+ 0Z002 E ERRCODE_STACKED_DIAGNOSTICS_ACCESSED_WITHOUT_ACTIVE_HANDLER stacked_diagnostics_accessed_without_active_handler
+
Section: Class 20 - Case Not Found
20000 E ERRCODE_CASE_NOT_FOUND case_not_found
*** ./src/pl/plpgsql/src/gram.y.orig 2011-05-18 19:41:56.755678378 +0200
--- ./src/pl/plpgsql/src/gram.y 2011-06-02 08:52:31.687830966 +0200
***************
*** 206,211 ****
--- 206,212 ----
%type <list> getdiag_list
%type <diagitem> getdiag_list_item
%type <ival> getdiag_item getdiag_target
+ %type <boolean> getdiag_opt
%type <ival> opt_scrollable
%type <fetch> opt_fetch_direction
***************
*** 250,256 ****
--- 251,259 ----
%token <keyword> K_CLOSE
%token <keyword> K_COLLATE
%token <keyword> K_CONSTANT
+ %token <keyword> K_CONTEXT
%token <keyword> K_CONTINUE
+ %token <keyword> K_CURRENT
%token <keyword> K_CURSOR
%token <keyword> K_DEBUG
%token <keyword> K_DECLARE
***************
*** 263,268 ****
--- 266,274 ----
%token <keyword> K_END
%token <keyword> K_ERRCODE
%token <keyword> K_ERROR
+ %token <keyword> K_EXCEPTION_CONTEXT
+ %token <keyword> K_EXCEPTION_DETAIL
+ %token <keyword> K_EXCEPTION_HINT
%token <keyword> K_EXCEPTION
%token <keyword> K_EXECUTE
%token <keyword> K_EXIT
***************
*** 284,289 ****
--- 290,296 ----
%token <keyword> K_LOG
%token <keyword> K_LOOP
%token <keyword> K_MESSAGE
+ %token <keyword> K_MESSAGE_TEXT
%token <keyword> K_MOVE
%token <keyword> K_NEXT
%token <keyword> K_NO
***************
*** 300,311 ****
--- 307,320 ----
%token <keyword> K_RELATIVE
%token <keyword> K_RESULT_OID
%token <keyword> K_RETURN
+ %token <keyword> K_RETURNED_SQLSTATE
%token <keyword> K_REVERSE
%token <keyword> K_ROWTYPE
%token <keyword> K_ROW_COUNT
%token <keyword> K_SCROLL
%token <keyword> K_SLICE
%token <keyword> K_SQLSTATE
+ %token <keyword> K_STACKED
%token <keyword> K_STRICT
%token <keyword> K_THEN
%token <keyword> K_TO
***************
*** 832,845 ****
}
;
! stmt_getdiag : K_GET K_DIAGNOSTICS getdiag_list ';'
{
PLpgSQL_stmt_getdiag *new;
new = palloc0(sizeof(PLpgSQL_stmt_getdiag));
new->cmd_type = PLPGSQL_STMT_GETDIAG;
new->lineno = plpgsql_location_to_lineno(@1);
! new->diag_items = $3;
$$ = (PLpgSQL_stmt *)new;
}
--- 841,892 ----
}
;
! stmt_getdiag : K_GET getdiag_opt K_DIAGNOSTICS getdiag_list ';'
{
PLpgSQL_stmt_getdiag *new;
+ ListCell *lc;
new = palloc0(sizeof(PLpgSQL_stmt_getdiag));
new->cmd_type = PLPGSQL_STMT_GETDIAG;
new->lineno = plpgsql_location_to_lineno(@1);
! new->is_stacked = $2;
! new->diag_items = $4;
!
! /*
! * stacked diagnostics statements allows only access
! * to exception data fields. whereas current diagnostics
! * statement disallow access to exception data fields.
! */
! foreach(lc, $4)
! {
! PLpgSQL_diag_item *ditem = (PLpgSQL_diag_item *) lfirst(lc);
!
! switch (ditem->kind)
! {
! /* these fields are disallowed in stacked diagnostics statement */
! case PLPGSQL_GETDIAG_ROW_COUNT:
! case PLPGSQL_GETDIAG_RESULT_OID:
! if ($2)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("cannot use ROW_COUNT or RESULT_OID inside stacked diagnostics statemet"),
! parser_errposition(@1)));
! break;
!
! /* these fields are disallowed in current diagnostics statement */
! case PLPGSQL_GETDIAG_ERROR_CONTEXT:
! case PLPGSQL_GETDIAG_ERROR_DETAIL:
! case PLPGSQL_GETDIAG_ERROR_HINT:
! case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
! case PLPGSQL_GETDIAG_MESSAGE_TEXT:
! if (!$2)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("EXCEPTION_CONTEXT or EXCEPTION_DETAIL or EXCEPTION_HINT or RETURNED_SQLSTATE or MESSAGE_TEXT are not allowed in current diagnostics statement"),
! parser_errposition(@1)));
!
! }
! }
$$ = (PLpgSQL_stmt *)new;
}
***************
*** 867,872 ****
--- 914,933 ----
}
;
+ getdiag_opt :
+ {
+ $$ = false;
+ }
+ | K_CURRENT
+ {
+ $$ = false;
+ }
+ | K_STACKED
+ {
+ $$ = true;
+ }
+ ;
+
getdiag_item :
{
int tok = yylex();
***************
*** 877,882 ****
--- 938,958 ----
else if (tok_is_keyword(tok, &yylval,
K_RESULT_OID, "result_oid"))
$$ = PLPGSQL_GETDIAG_RESULT_OID;
+ else if (tok_is_keyword(tok, &yylval,
+ K_EXCEPTION_DETAIL, "pg_exception_detail"))
+ $$ = PLPGSQL_GETDIAG_ERROR_DETAIL;
+ else if (tok_is_keyword(tok, &yylval,
+ K_EXCEPTION_HINT, "pg_exception_hint"))
+ $$ = PLPGSQL_GETDIAG_ERROR_HINT;
+ else if (tok_is_keyword(tok, &yylval,
+ K_EXCEPTION_CONTEXT, "pg_exception_context"))
+ $$ = PLPGSQL_GETDIAG_ERROR_CONTEXT;
+ else if (tok_is_keyword(tok, &yylval,
+ K_MESSAGE_TEXT, "message_text"))
+ $$ = PLPGSQL_GETDIAG_MESSAGE_TEXT;
+ else if (tok_is_keyword(tok, &yylval,
+ K_RETURNED_SQLSTATE, "returned_sqlstate"))
+ $$ = PLPGSQL_GETDIAG_RETURNED_SQLSTATE;
else
yyerror("unrecognized GET DIAGNOSTICS item");
}
***************
*** 2135,2146 ****
--- 2211,2226 ----
| K_ARRAY
| K_BACKWARD
| K_CONSTANT
+ | K_CURRENT
| K_CURSOR
| K_DEBUG
| K_DETAIL
| K_DUMP
| K_ERRCODE
| K_ERROR
+ | K_EXCEPTION_CONTEXT
+ | K_EXCEPTION_DETAIL
+ | K_EXCEPTION_HINT
| K_FIRST
| K_FORWARD
| K_HINT
***************
*** 2149,2154 ****
--- 2229,2235 ----
| K_LAST
| K_LOG
| K_MESSAGE
+ | K_MESSAGE_TEXT
| K_NEXT
| K_NO
| K_NOTICE
***************
*** 2157,2168 ****
--- 2238,2251 ----
| K_QUERY
| K_RELATIVE
| K_RESULT_OID
+ | K_RETURNED_SQLSTATE
| K_REVERSE
| K_ROW_COUNT
| K_ROWTYPE
| K_SCROLL
| K_SLICE
| K_SQLSTATE
+ | K_STACKED
| K_TYPE
| K_USE_COLUMN
| K_USE_VARIABLE
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2011-05-18 19:42:15.458152167 +0200
--- ./src/pl/plpgsql/src/pl_exec.c 2011-06-01 21:15:39.903684130 +0200
***************
*** 1449,1454 ****
--- 1449,1506 ----
ObjectIdGetDatum(estate->eval_lastoid),
OIDOID, &isnull);
break;
+ case PLPGSQL_GETDIAG_ERROR_DETAIL:
+ case PLPGSQL_GETDIAG_ERROR_HINT:
+ case PLPGSQL_GETDIAG_ERROR_CONTEXT:
+ case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
+ case PLPGSQL_GETDIAG_MESSAGE_TEXT:
+ {
+ char *strval = NULL;
+ Datum value;
+
+ /*
+ * Now a fields based on processing of Error Data
+ * are handled.
+ */
+ if (estate->cur_error == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_STACKED_DIAGNOSTICS_ACCESSED_WITHOUT_ACTIVE_HANDLER),
+ errmsg("stacked diagnostics accessed without active handler")));
+ switch (diag_item->kind)
+ {
+ case PLPGSQL_GETDIAG_ERROR_DETAIL:
+ strval = estate->cur_error->detail;
+ break;
+ case PLPGSQL_GETDIAG_ERROR_HINT:
+ strval = estate->cur_error->hint;
+ break;
+ case PLPGSQL_GETDIAG_ERROR_CONTEXT:
+ strval = estate->cur_error->context;
+ break;
+ case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
+ strval = unpack_sql_state(estate->cur_error->sqlerrcode);
+ break;
+ case PLPGSQL_GETDIAG_MESSAGE_TEXT:
+ strval = estate->cur_error->message;
+ }
+
+ if (strval != NULL)
+ {
+ value = PointerGetDatum(cstring_to_text(strval));
+ }
+ else
+ {
+ /*
+ * Diagnostics fields are never NULL, it should to return empty string
+ * instead.
+ */
+ value = PointerGetDatum(cstring_to_text(""));
+ }
+
+ exec_assign_value(estate, var,
+ value, TEXTOID, &isnull);
+ break;
+ }
default:
elog(ERROR, "unrecognized attribute request: %d",
*** ./src/pl/plpgsql/src/pl_funcs.c.orig 2011-05-21 20:59:52.557887117 +0200
--- ./src/pl/plpgsql/src/pl_funcs.c 2011-06-02 08:39:56.248144435 +0200
***************
*** 1389,1395 ****
ListCell *lc;
dump_ind();
! printf("GET DIAGNOSTICS ");
foreach(lc, stmt->diag_items)
{
PLpgSQL_diag_item *diag_item = (PLpgSQL_diag_item *) lfirst(lc);
--- 1389,1395 ----
ListCell *lc;
dump_ind();
! printf("GET %s DIAGNOSTICS ", stmt->is_stacked ? "STACKED" : "CURRENT");
foreach(lc, stmt->diag_items)
{
PLpgSQL_diag_item *diag_item = (PLpgSQL_diag_item *) lfirst(lc);
***************
*** 1409,1414 ****
--- 1409,1434 ----
printf("RESULT_OID");
break;
+ case PLPGSQL_GETDIAG_ERROR_CONTEXT:
+ printf("PG_EXCEPTION_CONTEXT");
+ break;
+
+ case PLPGSQL_GETDIAG_ERROR_DETAIL:
+ printf("PG_EXCEPTION_DETAIL");
+ break;
+
+ case PLPGSQL_GETDIAG_ERROR_HINT:
+ printf("PG_EXCEPTION_HINT");
+ break;
+
+ case PLPGSQL_GETDIAG_MESSAGE_TEXT:
+ printf("MESSAGE_TEXT");
+ break;
+
+ case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
+ printf("RETURNED_SQLSTATE");
+ break;
+
default:
printf("???");
break;
*** ./src/pl/plpgsql/src/plpgsql.h.orig 2011-05-18 19:42:06.402954431 +0200
--- ./src/pl/plpgsql/src/plpgsql.h 2011-06-01 19:21:55.444895044 +0200
***************
*** 126,132 ****
enum
{
PLPGSQL_GETDIAG_ROW_COUNT,
! PLPGSQL_GETDIAG_RESULT_OID
};
/* --------
--- 126,137 ----
enum
{
PLPGSQL_GETDIAG_ROW_COUNT,
! PLPGSQL_GETDIAG_RESULT_OID,
! PLPGSQL_GETDIAG_ERROR_CONTEXT,
! PLPGSQL_GETDIAG_ERROR_DETAIL,
! PLPGSQL_GETDIAG_ERROR_HINT,
! PLPGSQL_GETDIAG_RETURNED_SQLSTATE,
! PLPGSQL_GETDIAG_MESSAGE_TEXT
};
/* --------
***************
*** 321,326 ****
--- 326,334 ----
{ /* One EXCEPTION condition name */
int sqlerrstate; /* SQLSTATE code */
char *condname; /* condition name (for debugging) */
+ bool needs_edata; /* needs access to edata content */
+ bool needs_stacked_edata; /* needs push content of edata to diagnostics stack */
+ bool needs_evaluate_context; /* needs to materialize a function context */
struct PLpgSQL_condition *next;
} PLpgSQL_condition;
***************
*** 377,382 ****
--- 385,391 ----
int cmd_type;
int lineno;
List *diag_items; /* List of PLpgSQL_diag_item */
+ bool is_stacked; /* true when it access a second area of diagnostics info */
} PLpgSQL_stmt_getdiag;
*** ./src/pl/plpgsql/src/pl_scanner.c.orig 2011-05-18 14:22:29.000000000 +0200
--- ./src/pl/plpgsql/src/pl_scanner.c 2011-06-01 21:18:47.632566225 +0200
***************
*** 110,115 ****
--- 110,116 ----
PG_KEYWORD("array", K_ARRAY, UNRESERVED_KEYWORD)
PG_KEYWORD("backward", K_BACKWARD, UNRESERVED_KEYWORD)
PG_KEYWORD("constant", K_CONSTANT, UNRESERVED_KEYWORD)
+ PG_KEYWORD("current", K_CURRENT, UNRESERVED_KEYWORD)
PG_KEYWORD("cursor", K_CURSOR, UNRESERVED_KEYWORD)
PG_KEYWORD("debug", K_DEBUG, UNRESERVED_KEYWORD)
PG_KEYWORD("detail", K_DETAIL, UNRESERVED_KEYWORD)
***************
*** 124,143 ****
--- 125,150 ----
PG_KEYWORD("last", K_LAST, UNRESERVED_KEYWORD)
PG_KEYWORD("log", K_LOG, UNRESERVED_KEYWORD)
PG_KEYWORD("message", K_MESSAGE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("message_text", K_MESSAGE_TEXT, UNRESERVED_KEYWORD)
PG_KEYWORD("next", K_NEXT, UNRESERVED_KEYWORD)
PG_KEYWORD("no", K_NO, UNRESERVED_KEYWORD)
PG_KEYWORD("notice", K_NOTICE, UNRESERVED_KEYWORD)
PG_KEYWORD("option", K_OPTION, UNRESERVED_KEYWORD)
+ PG_KEYWORD("pg_exception_context", K_EXCEPTION_CONTEXT, UNRESERVED_KEYWORD)
+ PG_KEYWORD("pg_exception_detail", K_EXCEPTION_DETAIL, UNRESERVED_KEYWORD)
+ PG_KEYWORD("pg_exception_hint", K_EXCEPTION_HINT, UNRESERVED_KEYWORD)
PG_KEYWORD("prior", K_PRIOR, UNRESERVED_KEYWORD)
PG_KEYWORD("query", K_QUERY, UNRESERVED_KEYWORD)
PG_KEYWORD("relative", K_RELATIVE, UNRESERVED_KEYWORD)
PG_KEYWORD("result_oid", K_RESULT_OID, UNRESERVED_KEYWORD)
+ PG_KEYWORD("returned_sqlstate", K_RETURNED_SQLSTATE, UNRESERVED_KEYWORD)
PG_KEYWORD("reverse", K_REVERSE, UNRESERVED_KEYWORD)
PG_KEYWORD("row_count", K_ROW_COUNT, UNRESERVED_KEYWORD)
PG_KEYWORD("rowtype", K_ROWTYPE, UNRESERVED_KEYWORD)
PG_KEYWORD("scroll", K_SCROLL, UNRESERVED_KEYWORD)
PG_KEYWORD("slice", K_SLICE, UNRESERVED_KEYWORD)
PG_KEYWORD("sqlstate", K_SQLSTATE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("stacked", K_STACKED, UNRESERVED_KEYWORD)
PG_KEYWORD("type", K_TYPE, UNRESERVED_KEYWORD)
PG_KEYWORD("use_column", K_USE_COLUMN, UNRESERVED_KEYWORD)
PG_KEYWORD("use_variable", K_USE_VARIABLE, UNRESERVED_KEYWORD)
*** ./src/test/regress/expected/plpgsql.out.orig 2011-05-18 14:22:29.000000000 +0200
--- ./src/test/regress/expected/plpgsql.out 2011-06-02 09:58:34.000000000 +0200
***************
*** 4434,4436 ****
--- 4434,4511 ----
drop function foreach_test(anyarray);
drop type xy_tuple;
+ -- access to exception data
+ create or replace function bad_function()
+ returns int as $$
+ declare v int := 0;
+ begin
+ return 10 / v;
+ end;
+ $$ language plpgsql;
+ create or replace function raise_function()
+ returns void as $$
+ begin
+ raise exception 'custom exception'
+ using detail = 'some detail of custom exception',
+ hint = 'some hint related to custom exception';
+ end;
+ $$ language plpgsql;
+ create or replace function stacked_diagnostics_test01()
+ returns void as $$
+ declare _sqlstate text; _message text; _context text;
+ begin
+ perform bad_function();
+ exception when others then
+ get stacked diagnostics
+ _sqlstate = returned_sqlstate,
+ _message = message_text,
+ _context = pg_exception_context;
+ raise notice 'sqlstate: %, message: %, context: [%]', _sqlstate, _message, replace(_context, e'\n', ' <- ');
+ end;
+ $$ language plpgsql;
+ select stacked_diagnostics_test01();
+ NOTICE: sqlstate: 22012, message: division by zero, context: [PL/pgSQL function "bad_function" line 4 at RETURN <- SQL statement "SELECT bad_function()" <- PL/pgSQL function "stacked_diagnostics_test01" line 4 at PERFORM]
+ stacked_diagnostics_test01
+ ----------------------------
+
+ (1 row)
+
+ create or replace function stacked_diagnostics_test02()
+ returns void as $$
+ declare _detail text; _hint text; _message text;
+ begin
+ perform raise_function();
+ exception when others then
+ get stacked diagnostics
+ _message = message_text,
+ _detail = pg_exception_detail,
+ _hint = pg_exception_hint;
+ raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
+ end;
+ $$ language plpgsql;
+ select stacked_diagnostics_test02();
+ NOTICE: message: custom exception, detail: some detail of custom exception, hint: some hint related to custom exception
+ stacked_diagnostics_test02
+ ----------------------------
+
+ (1 row)
+
+ -- should to fail, cannot use stacked diagnostics statement outside handler
+ create or replace function stacked_diagnostics_test02()
+ returns void as $$
+ declare _detail text; _hint text; _message text;
+ begin
+ get stacked diagnostics
+ _message = message_text,
+ _detail = pg_exception_detail,
+ _hint = pg_exception_hint;
+ raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
+ end;
+ $$ language plpgsql;
+ select stacked_diagnostics_test02();
+ ERROR: stacked diagnostics accessed without active handler
+ CONTEXT: PL/pgSQL function "stacked_diagnostics_test02" line 4 at GET DIAGNOSTICS
+ drop function bad_function();
+ drop function raise_function();
+ drop function stacked_diagnostics_test01();
+ drop function stacked_diagnostics_test02();
*** ./src/test/regress/sql/plpgsql.sql.orig 2011-05-18 14:22:30.000000000 +0200
--- ./src/test/regress/sql/plpgsql.sql 2011-06-02 09:57:22.040084273 +0200
***************
*** 3489,3491 ****
--- 3489,3563 ----
drop function foreach_test(anyarray);
drop type xy_tuple;
+
+
+ -- access to exception data
+ create or replace function bad_function()
+ returns int as $$
+ declare v int := 0;
+ begin
+ return 10 / v;
+ end;
+ $$ language plpgsql;
+
+ create or replace function raise_function()
+ returns void as $$
+ begin
+ raise exception 'custom exception'
+ using detail = 'some detail of custom exception',
+ hint = 'some hint related to custom exception';
+ end;
+ $$ language plpgsql;
+
+ create or replace function stacked_diagnostics_test01()
+ returns void as $$
+ declare _sqlstate text; _message text; _context text;
+ begin
+ perform bad_function();
+ exception when others then
+ get stacked diagnostics
+ _sqlstate = returned_sqlstate,
+ _message = message_text,
+ _context = pg_exception_context;
+ raise notice 'sqlstate: %, message: %, context: [%]', _sqlstate, _message, replace(_context, e'\n', ' <- ');
+ end;
+ $$ language plpgsql;
+
+ select stacked_diagnostics_test01();
+
+ create or replace function stacked_diagnostics_test02()
+ returns void as $$
+ declare _detail text; _hint text; _message text;
+ begin
+ perform raise_function();
+ exception when others then
+ get stacked diagnostics
+ _message = message_text,
+ _detail = pg_exception_detail,
+ _hint = pg_exception_hint;
+ raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
+ end;
+ $$ language plpgsql;
+
+ select stacked_diagnostics_test02();
+
+ -- should to fail, cannot use stacked diagnostics statement outside handler
+
+ create or replace function stacked_diagnostics_test02()
+ returns void as $$
+ declare _detail text; _hint text; _message text;
+ begin
+ get stacked diagnostics
+ _message = message_text,
+ _detail = pg_exception_detail,
+ _hint = pg_exception_hint;
+ raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
+ end;
+ $$ language plpgsql;
+
+ select stacked_diagnostics_test02();
+
+ drop function bad_function();
+ drop function raise_function();
+ drop function stacked_diagnostics_test01();
+ drop function stacked_diagnostics_test02();
Re: patch: enhanced get diagnostics statement 2
(2011/06/02 17:39), Pavel Stehule wrote:
This patch enhances a GET DIAGNOSTICS statement functionality. It adds
a possibility of access to exception's data. These data are stored on
stack when exception's handler is activated - and these data are
access-able everywhere inside handler. It has a different behave (the
content is immutable inside handler) and therefore it has modified
syntax (use keyword STACKED). This implementation is in conformance
with ANSI SQL and SQL/PSM - implemented two standard fields -
RETURNED_SQLSTATE and MESSAGE_TEXT and three PostgreSQL specific
fields - PG_EXCEPTION_DETAIL, PG_EXCEPTION_HINT and
PG_EXCEPTION_CONTEXT.The GET STACKED DIAGNOSTICS statement is allowed only inside
exception's handler. When it is used outside handler, then diagnostics
exception 0Z002 is raised.This patch has no impact on performance. It is just interface to
existing stacked 'edata' structure. This patch doesn't change a
current behave of GET DIAGNOSTICS statement.CREATE OR REPLACE FUNCTION public.stacked_diagnostics_test02()
RETURNS void
LANGUAGE plpgsql
AS $function$
declare _detail text; _hint text; _message text;
begin
perform ...
exception when others then
get stacked diagnostics
_message = message_text,
_detail = pg_exception_detail,
_hint = pg_exception_hint;
raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
end;
$function$All regress tests was passed.
Hi Pavel,
I've reviewed your patch according to the page "Reviewing a patch".
During the review, I referred to Working-Draft of SQL 2003 to confirm
the SQL specs.
Submission review
=================
* The patch is in context diff format.
* The patch couldn't be applied cleanly to the current head. But it
requires only one hunk to be offset, and it could be fixed easily.
I noticed that new variables needs_xxx, which were added to struct
PLpgSQL_condition, are not used at all. They should be removed, or
something might be overlooked.
* The patch includes reasonable regression tests. The patch also
includes hunks for pl/pgsql document which describes new
feature. But it would need some corrections:
- folding too-long lines
- fixing some grammatical errors (maybe)
- clarify difference between CURRENT and STACKED
I think that adding new section for GET STACKED DIAGNOSTICS would help
to clarify the difference, because the keyword STACKED can be used only
in exception clause, and available information is different from the one
available for GET CURRENT DIAGNOSTICS. Please find attached a patch
which includes a proposal for document though it still needs review by
English speaker.
Usability review
================
* The patch extends GET DIAGNOSTICS syntax to accept new keywords
CURRENT and STACKED, which are described in the SQL/PSM standard. This
feature allows us to retrieve exception information in EXCEPTION clause.
Naming of PG-specific fields might be debatable.
* I think it's useful to get detailed information inside EXCEPTION clause.
* We don't have this feature yet.
* This patch follows SQL spec of GET DIAGNOSTICS, and extends about
PG-specific variables.
* pg_dump support is not required for this feature.
* AFAICS, this patch doesn't have any danger, such as breakage of
backward compatibility.
Feature test
============
* The new feature introduced by the patch works well.
I tested about:
- CURRENT doesn't affect existing feature
- STACKED couldn't be used outside EXCEPTION clause
- Values could be retrieved via RETURNED_SQLSTATE, MESSAGE_TEXT,
PG_EXCEPTION_DETAIL, PG_EXCEPTION_HINT and PG_EXCEPTION_CONTEXT
- Invalid item names properly cause error.
* I'm not so familiar to pl/pgsql, but ISTM that enough cases are
considered about newly added diagnostics items.
* I didn't see any crash during my tests.
In conclusion, this patch still needs some effort to be "Ready for
Committer", so I'll push it back to "Waiting on Author".
Regards,
--
Shigeru Hanada
Attachments:
fix_getdiag_doc.patchtext/plain; name=fix_getdiag_doc.patchDownload
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 3d07b6e..7df69a7 100644
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*************** EXECUTE format('UPDATE tbl SET %I = $1 W
*** 1387,1393 ****
command, which has the form:
<synopsis>
! GET <optional> CURRENT | STACKED </optional> DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
</synopsis>
This command allows retrieval of system status indicators. Each
--- 1387,1393 ----
command, which has the form:
<synopsis>
! GET <optional> CURRENT </optional> DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
</synopsis>
This command allows retrieval of system status indicators. Each
*************** GET DIAGNOSTICS integer_var = ROW_COUNT;
*** 1486,1506 ****
affect only the current function.
</para>
<para>
! Inside a exception handler is possible to use a stacked diagnostics statement. It
! allows a access to exception's data: the <varname>RETURNED_SQLSTATE</varname> contains
! a SQLSTATE of handled exception. <varname>MESSAGE_TEXT</varname> contains a message text,
! <varname>PG_EXCEPTION_DETAIL</varname> has a text that is shown as exception detail,
! <varname>PG_EXCEPTION_HINT</varname> has a hint related to catched exception.
! <varname>PG_EXCEPTION_CONTEXT</varname> contains a lines that describes call stack. These
! variables holds a text value. When some field of exception are not filled, then related
! variable contains a empty string,
</para>
<para>
! An example:
<programlisting>
BEGIN
...
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
--- 1486,1523 ----
affect only the current function.
</para>
+ </sect2>
+
+ <sect2 id="plpgsql-exception-diagnostics">
+ <title>Obtaining the Exception Status</title>
+
<para>
! Inside an exception handler, it's possible to retrieve detailed
! information about the exception which is currently handled, with using a
! <command>GET STACKED DIAGNOSTICS</command> command, which has the form:
!
! <synopsis>
! GET STACKED DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
! </synopsis>
</para>
<para>
! It allows you to access to exception's data: the
! <varname>RETURNED_SQLSTATE</varname> contains a SQLSTATE of handled
! exception. <varname>MESSAGE_TEXT</varname> contains a message text,
! <varname>PG_EXCEPTION_DETAIL</varname> has a text that is shown as
! exception detail, <varname>PG_EXCEPTION_HINT</varname> has a hint
! related to catched exception. <varname>PG_EXCEPTION_CONTEXT</varname>
! contains a lines that describes call stack. These variables holds a text
! value. When some of exception fields are not filled, then such variable
! contains an empty string. An example is:
<programlisting>
+ DECLARE
+ text_var1 text;
+ text_var2 text;
+ text_var3 text;
BEGIN
+ -- some processing which might cause an exception
...
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
Re: patch: enhanced get diagnostics statement 2
Hello
thank you very much for review.
I cleaned patch and merged your documentation patch
I hope, this is all - a language correction should do some native speaker
Regards
Pavel Stehule
2011/7/6 Shigeru Hanada <shigeru.hanada@gmail.com>:
Show quoted text
(2011/06/02 17:39), Pavel Stehule wrote:
This patch enhances a GET DIAGNOSTICS statement functionality. It adds
a possibility of access to exception's data. These data are stored on
stack when exception's handler is activated - and these data are
access-able everywhere inside handler. It has a different behave (the
content is immutable inside handler) and therefore it has modified
syntax (use keyword STACKED). This implementation is in conformance
with ANSI SQL and SQL/PSM - implemented two standard fields -
RETURNED_SQLSTATE and MESSAGE_TEXT and three PostgreSQL specific
fields - PG_EXCEPTION_DETAIL, PG_EXCEPTION_HINT and
PG_EXCEPTION_CONTEXT.The GET STACKED DIAGNOSTICS statement is allowed only inside
exception's handler. When it is used outside handler, then diagnostics
exception 0Z002 is raised.This patch has no impact on performance. It is just interface to
existing stacked 'edata' structure. This patch doesn't change a
current behave of GET DIAGNOSTICS statement.CREATE OR REPLACE FUNCTION public.stacked_diagnostics_test02()
RETURNS void
LANGUAGE plpgsql
AS $function$
declare _detail text; _hint text; _message text;
begin
perform ...
exception when others then
get stacked diagnostics
_message = message_text,
_detail = pg_exception_detail,
_hint = pg_exception_hint;
raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
end;
$function$All regress tests was passed.
Hi Pavel,
I've reviewed your patch according to the page "Reviewing a patch".
During the review, I referred to Working-Draft of SQL 2003 to confirm
the SQL specs.Submission review
=================
* The patch is in context diff format.
* The patch couldn't be applied cleanly to the current head. But it
requires only one hunk to be offset, and it could be fixed easily.
I noticed that new variables needs_xxx, which were added to struct
PLpgSQL_condition, are not used at all. They should be removed, or
something might be overlooked.
* The patch includes reasonable regression tests. The patch also
includes hunks for pl/pgsql document which describes new
feature. But it would need some corrections:
- folding too-long lines
- fixing some grammatical errors (maybe)
- clarify difference between CURRENT and STACKED
I think that adding new section for GET STACKED DIAGNOSTICS would help
to clarify the difference, because the keyword STACKED can be used only
in exception clause, and available information is different from the one
available for GET CURRENT DIAGNOSTICS. Please find attached a patch
which includes a proposal for document though it still needs review by
English speaker.Usability review
================
* The patch extends GET DIAGNOSTICS syntax to accept new keywords
CURRENT and STACKED, which are described in the SQL/PSM standard. This
feature allows us to retrieve exception information in EXCEPTION clause.
Naming of PG-specific fields might be debatable.
* I think it's useful to get detailed information inside EXCEPTION clause.
* We don't have this feature yet.
* This patch follows SQL spec of GET DIAGNOSTICS, and extends about
PG-specific variables.
* pg_dump support is not required for this feature.
* AFAICS, this patch doesn't have any danger, such as breakage of
backward compatibility.Feature test
============
* The new feature introduced by the patch works well.
I tested about:
- CURRENT doesn't affect existing feature
- STACKED couldn't be used outside EXCEPTION clause
- Values could be retrieved via RETURNED_SQLSTATE, MESSAGE_TEXT,
PG_EXCEPTION_DETAIL, PG_EXCEPTION_HINT and PG_EXCEPTION_CONTEXT
- Invalid item names properly cause error.
* I'm not so familiar to pl/pgsql, but ISTM that enough cases are
considered about newly added diagnostics items.
* I didn't see any crash during my tests.In conclusion, this patch still needs some effort to be "Ready for
Committer", so I'll push it back to "Waiting on Author".Regards,
--
Shigeru Hanada
Attachments:
getdiag-2.difftext/x-patch; charset=US-ASCII; name=getdiag-2.diffDownload
*** ./doc/src/sgml/plpgsql.sgml.orig 2011-07-07 09:03:07.135669770 +0200
--- ./doc/src/sgml/plpgsql.sgml 2011-07-07 09:12:20.443762372 +0200
***************
*** 1387,1393 ****
command, which has the form:
<synopsis>
! GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
</synopsis>
This command allows retrieval of system status indicators. Each
--- 1387,1393 ----
command, which has the form:
<synopsis>
! GET <optional> CURRENT </optional> DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
</synopsis>
This command allows retrieval of system status indicators. Each
***************
*** 1488,1493 ****
--- 1488,1535 ----
</sect2>
+ <sect2 id="plpgsql-exception-diagnostics">
+ <title>Obtaining the Exception Status</title>
+
+ <para>
+ Inside an exception handler, it's possible to retrieve detailed
+ information about the exception which is currently handled, with using a
+ <command>GET STACKED DIAGNOSTICS</command> command, which has the form:
+
+ <synopsis>
+ GET STACKED DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
+ </synopsis>
+ </para>
+
+ <para>
+ It allows you to access to exception's data: the
+ <varname>RETURNED_SQLSTATE</varname> contains a SQLSTATE of handled
+ exception. <varname>MESSAGE_TEXT</varname> contains a message text,
+ <varname>PG_EXCEPTION_DETAIL</varname> has a text that is shown as
+ exception detail, <varname>PG_EXCEPTION_HINT</varname> has a hint
+ related to catched exception. <varname>PG_EXCEPTION_CONTEXT</varname>
+ contains a lines that describes call stack. These variables holds a text
+ value. When some of exception fields are not filled, then such variable
+ contains an empty string. An example is:
+ <programlisting>
+ DECLARE
+ text_var1 text;
+ text_var2 text;
+ text_var3 text;
+ BEGIN
+ -- some processing which might cause an exception
+ ...
+ EXCEPTION WHEN OTHERS THEN
+ GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
+ text_var2 = PG_EXCEPTION_DETAIL,
+ text_var3 = PG_EXCEPTION_HINT;
+ END;
+ </programlisting>
+ </para>
+
+
+ </sect2>
+
<sect2 id="plpgsql-statements-null">
<title>Doing Nothing At All</title>
*** ./src/backend/utils/errcodes.txt.orig 2011-07-07 09:03:37.097420794 +0200
--- ./src/backend/utils/errcodes.txt 2011-07-07 09:22:10.915335961 +0200
***************
*** 132,137 ****
--- 132,140 ----
0P000 E ERRCODE_INVALID_ROLE_SPECIFICATION invalid_role_specification
+ Section: Class 0Z - Diagnostics Exception
+ 0Z002 E ERRCODE_STACKED_DIAGNOSTICS_ACCESSED_WITHOUT_ACTIVE_HANDLER stacked_diagnostics_accessed_without_active_handler
+
Section: Class 20 - Case Not Found
20000 E ERRCODE_CASE_NOT_FOUND case_not_found
*** ./src/pl/plpgsql/src/gram.y.orig 2011-07-07 09:03:55.649265940 +0200
--- ./src/pl/plpgsql/src/gram.y 2011-07-07 09:08:55.526687220 +0200
***************
*** 206,211 ****
--- 206,212 ----
%type <list> getdiag_list
%type <diagitem> getdiag_list_item
%type <ival> getdiag_item getdiag_target
+ %type <boolean> getdiag_opt
%type <ival> opt_scrollable
%type <fetch> opt_fetch_direction
***************
*** 250,256 ****
--- 251,259 ----
%token <keyword> K_CLOSE
%token <keyword> K_COLLATE
%token <keyword> K_CONSTANT
+ %token <keyword> K_CONTEXT
%token <keyword> K_CONTINUE
+ %token <keyword> K_CURRENT
%token <keyword> K_CURSOR
%token <keyword> K_DEBUG
%token <keyword> K_DECLARE
***************
*** 263,268 ****
--- 266,274 ----
%token <keyword> K_END
%token <keyword> K_ERRCODE
%token <keyword> K_ERROR
+ %token <keyword> K_EXCEPTION_CONTEXT
+ %token <keyword> K_EXCEPTION_DETAIL
+ %token <keyword> K_EXCEPTION_HINT
%token <keyword> K_EXCEPTION
%token <keyword> K_EXECUTE
%token <keyword> K_EXIT
***************
*** 284,289 ****
--- 290,296 ----
%token <keyword> K_LOG
%token <keyword> K_LOOP
%token <keyword> K_MESSAGE
+ %token <keyword> K_MESSAGE_TEXT
%token <keyword> K_MOVE
%token <keyword> K_NEXT
%token <keyword> K_NO
***************
*** 300,311 ****
--- 307,320 ----
%token <keyword> K_RELATIVE
%token <keyword> K_RESULT_OID
%token <keyword> K_RETURN
+ %token <keyword> K_RETURNED_SQLSTATE
%token <keyword> K_REVERSE
%token <keyword> K_ROWTYPE
%token <keyword> K_ROW_COUNT
%token <keyword> K_SCROLL
%token <keyword> K_SLICE
%token <keyword> K_SQLSTATE
+ %token <keyword> K_STACKED
%token <keyword> K_STRICT
%token <keyword> K_THEN
%token <keyword> K_TO
***************
*** 832,845 ****
}
;
! stmt_getdiag : K_GET K_DIAGNOSTICS getdiag_list ';'
{
PLpgSQL_stmt_getdiag *new;
new = palloc0(sizeof(PLpgSQL_stmt_getdiag));
new->cmd_type = PLPGSQL_STMT_GETDIAG;
new->lineno = plpgsql_location_to_lineno(@1);
! new->diag_items = $3;
$$ = (PLpgSQL_stmt *)new;
}
--- 841,892 ----
}
;
! stmt_getdiag : K_GET getdiag_opt K_DIAGNOSTICS getdiag_list ';'
{
PLpgSQL_stmt_getdiag *new;
+ ListCell *lc;
new = palloc0(sizeof(PLpgSQL_stmt_getdiag));
new->cmd_type = PLPGSQL_STMT_GETDIAG;
new->lineno = plpgsql_location_to_lineno(@1);
! new->is_stacked = $2;
! new->diag_items = $4;
!
! /*
! * stacked diagnostics statements allows only access
! * to exception data fields. whereas current diagnostics
! * statement disallow access to exception data fields.
! */
! foreach(lc, $4)
! {
! PLpgSQL_diag_item *ditem = (PLpgSQL_diag_item *) lfirst(lc);
!
! switch (ditem->kind)
! {
! /* these fields are disallowed in stacked diagnostics statement */
! case PLPGSQL_GETDIAG_ROW_COUNT:
! case PLPGSQL_GETDIAG_RESULT_OID:
! if ($2)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("cannot use ROW_COUNT or RESULT_OID inside stacked diagnostics statemet"),
! parser_errposition(@1)));
! break;
!
! /* these fields are disallowed in current diagnostics statement */
! case PLPGSQL_GETDIAG_ERROR_CONTEXT:
! case PLPGSQL_GETDIAG_ERROR_DETAIL:
! case PLPGSQL_GETDIAG_ERROR_HINT:
! case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
! case PLPGSQL_GETDIAG_MESSAGE_TEXT:
! if (!$2)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("EXCEPTION_CONTEXT or EXCEPTION_DETAIL or EXCEPTION_HINT or RETURNED_SQLSTATE or MESSAGE_TEXT are not allowed in current diagnostics statement"),
! parser_errposition(@1)));
!
! }
! }
$$ = (PLpgSQL_stmt *)new;
}
***************
*** 867,872 ****
--- 914,933 ----
}
;
+ getdiag_opt :
+ {
+ $$ = false;
+ }
+ | K_CURRENT
+ {
+ $$ = false;
+ }
+ | K_STACKED
+ {
+ $$ = true;
+ }
+ ;
+
getdiag_item :
{
int tok = yylex();
***************
*** 877,882 ****
--- 938,958 ----
else if (tok_is_keyword(tok, &yylval,
K_RESULT_OID, "result_oid"))
$$ = PLPGSQL_GETDIAG_RESULT_OID;
+ else if (tok_is_keyword(tok, &yylval,
+ K_EXCEPTION_DETAIL, "pg_exception_detail"))
+ $$ = PLPGSQL_GETDIAG_ERROR_DETAIL;
+ else if (tok_is_keyword(tok, &yylval,
+ K_EXCEPTION_HINT, "pg_exception_hint"))
+ $$ = PLPGSQL_GETDIAG_ERROR_HINT;
+ else if (tok_is_keyword(tok, &yylval,
+ K_EXCEPTION_CONTEXT, "pg_exception_context"))
+ $$ = PLPGSQL_GETDIAG_ERROR_CONTEXT;
+ else if (tok_is_keyword(tok, &yylval,
+ K_MESSAGE_TEXT, "message_text"))
+ $$ = PLPGSQL_GETDIAG_MESSAGE_TEXT;
+ else if (tok_is_keyword(tok, &yylval,
+ K_RETURNED_SQLSTATE, "returned_sqlstate"))
+ $$ = PLPGSQL_GETDIAG_RETURNED_SQLSTATE;
else
yyerror("unrecognized GET DIAGNOSTICS item");
}
***************
*** 2135,2146 ****
--- 2211,2226 ----
| K_ARRAY
| K_BACKWARD
| K_CONSTANT
+ | K_CURRENT
| K_CURSOR
| K_DEBUG
| K_DETAIL
| K_DUMP
| K_ERRCODE
| K_ERROR
+ | K_EXCEPTION_CONTEXT
+ | K_EXCEPTION_DETAIL
+ | K_EXCEPTION_HINT
| K_FIRST
| K_FORWARD
| K_HINT
***************
*** 2149,2154 ****
--- 2229,2235 ----
| K_LAST
| K_LOG
| K_MESSAGE
+ | K_MESSAGE_TEXT
| K_NEXT
| K_NO
| K_NOTICE
***************
*** 2157,2168 ****
--- 2238,2251 ----
| K_QUERY
| K_RELATIVE
| K_RESULT_OID
+ | K_RETURNED_SQLSTATE
| K_REVERSE
| K_ROW_COUNT
| K_ROWTYPE
| K_SCROLL
| K_SLICE
| K_SQLSTATE
+ | K_STACKED
| K_TYPE
| K_USE_COLUMN
| K_USE_VARIABLE
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2011-07-07 08:54:22.420484994 +0200
--- ./src/pl/plpgsql/src/pl_exec.c 2011-07-07 09:08:55.529687188 +0200
***************
*** 1450,1455 ****
--- 1450,1507 ----
ObjectIdGetDatum(estate->eval_lastoid),
OIDOID, &isnull);
break;
+ case PLPGSQL_GETDIAG_ERROR_DETAIL:
+ case PLPGSQL_GETDIAG_ERROR_HINT:
+ case PLPGSQL_GETDIAG_ERROR_CONTEXT:
+ case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
+ case PLPGSQL_GETDIAG_MESSAGE_TEXT:
+ {
+ char *strval = NULL;
+ Datum value;
+
+ /*
+ * Now a fields based on processing of Error Data
+ * are handled.
+ */
+ if (estate->cur_error == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_STACKED_DIAGNOSTICS_ACCESSED_WITHOUT_ACTIVE_HANDLER),
+ errmsg("stacked diagnostics accessed without active handler")));
+ switch (diag_item->kind)
+ {
+ case PLPGSQL_GETDIAG_ERROR_DETAIL:
+ strval = estate->cur_error->detail;
+ break;
+ case PLPGSQL_GETDIAG_ERROR_HINT:
+ strval = estate->cur_error->hint;
+ break;
+ case PLPGSQL_GETDIAG_ERROR_CONTEXT:
+ strval = estate->cur_error->context;
+ break;
+ case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
+ strval = unpack_sql_state(estate->cur_error->sqlerrcode);
+ break;
+ case PLPGSQL_GETDIAG_MESSAGE_TEXT:
+ strval = estate->cur_error->message;
+ }
+
+ if (strval != NULL)
+ {
+ value = PointerGetDatum(cstring_to_text(strval));
+ }
+ else
+ {
+ /*
+ * Diagnostics fields are never NULL, it should to return empty string
+ * instead.
+ */
+ value = PointerGetDatum(cstring_to_text(""));
+ }
+
+ exec_assign_value(estate, var,
+ value, TEXTOID, &isnull);
+ break;
+ }
default:
elog(ERROR, "unrecognized attribute request: %d",
*** ./src/pl/plpgsql/src/pl_funcs.c.orig 2011-07-07 09:04:22.625039900 +0200
--- ./src/pl/plpgsql/src/pl_funcs.c 2011-07-07 09:08:55.531687172 +0200
***************
*** 1389,1395 ****
ListCell *lc;
dump_ind();
! printf("GET DIAGNOSTICS ");
foreach(lc, stmt->diag_items)
{
PLpgSQL_diag_item *diag_item = (PLpgSQL_diag_item *) lfirst(lc);
--- 1389,1395 ----
ListCell *lc;
dump_ind();
! printf("GET %s DIAGNOSTICS ", stmt->is_stacked ? "STACKED" : "CURRENT");
foreach(lc, stmt->diag_items)
{
PLpgSQL_diag_item *diag_item = (PLpgSQL_diag_item *) lfirst(lc);
***************
*** 1409,1414 ****
--- 1409,1434 ----
printf("RESULT_OID");
break;
+ case PLPGSQL_GETDIAG_ERROR_CONTEXT:
+ printf("PG_EXCEPTION_CONTEXT");
+ break;
+
+ case PLPGSQL_GETDIAG_ERROR_DETAIL:
+ printf("PG_EXCEPTION_DETAIL");
+ break;
+
+ case PLPGSQL_GETDIAG_ERROR_HINT:
+ printf("PG_EXCEPTION_HINT");
+ break;
+
+ case PLPGSQL_GETDIAG_MESSAGE_TEXT:
+ printf("MESSAGE_TEXT");
+ break;
+
+ case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
+ printf("RETURNED_SQLSTATE");
+ break;
+
default:
printf("???");
break;
*** ./src/pl/plpgsql/src/plpgsql.h.orig 2011-07-07 09:04:39.560897500 +0200
--- ./src/pl/plpgsql/src/plpgsql.h 2011-07-07 09:17:18.994983475 +0200
***************
*** 126,132 ****
enum
{
PLPGSQL_GETDIAG_ROW_COUNT,
! PLPGSQL_GETDIAG_RESULT_OID
};
/* --------
--- 126,137 ----
enum
{
PLPGSQL_GETDIAG_ROW_COUNT,
! PLPGSQL_GETDIAG_RESULT_OID,
! PLPGSQL_GETDIAG_ERROR_CONTEXT,
! PLPGSQL_GETDIAG_ERROR_DETAIL,
! PLPGSQL_GETDIAG_ERROR_HINT,
! PLPGSQL_GETDIAG_RETURNED_SQLSTATE,
! PLPGSQL_GETDIAG_MESSAGE_TEXT
};
/* --------
***************
*** 377,382 ****
--- 382,388 ----
int cmd_type;
int lineno;
List *diag_items; /* List of PLpgSQL_diag_item */
+ bool is_stacked; /* true when it access a second area of diagnostics info */
} PLpgSQL_stmt_getdiag;
*** ./src/pl/plpgsql/src/pl_scanner.c.orig 2011-07-07 09:04:55.496763171 +0200
--- ./src/pl/plpgsql/src/pl_scanner.c 2011-07-07 09:08:55.532687164 +0200
***************
*** 110,115 ****
--- 110,116 ----
PG_KEYWORD("array", K_ARRAY, UNRESERVED_KEYWORD)
PG_KEYWORD("backward", K_BACKWARD, UNRESERVED_KEYWORD)
PG_KEYWORD("constant", K_CONSTANT, UNRESERVED_KEYWORD)
+ PG_KEYWORD("current", K_CURRENT, UNRESERVED_KEYWORD)
PG_KEYWORD("cursor", K_CURSOR, UNRESERVED_KEYWORD)
PG_KEYWORD("debug", K_DEBUG, UNRESERVED_KEYWORD)
PG_KEYWORD("detail", K_DETAIL, UNRESERVED_KEYWORD)
***************
*** 124,143 ****
--- 125,150 ----
PG_KEYWORD("last", K_LAST, UNRESERVED_KEYWORD)
PG_KEYWORD("log", K_LOG, UNRESERVED_KEYWORD)
PG_KEYWORD("message", K_MESSAGE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("message_text", K_MESSAGE_TEXT, UNRESERVED_KEYWORD)
PG_KEYWORD("next", K_NEXT, UNRESERVED_KEYWORD)
PG_KEYWORD("no", K_NO, UNRESERVED_KEYWORD)
PG_KEYWORD("notice", K_NOTICE, UNRESERVED_KEYWORD)
PG_KEYWORD("option", K_OPTION, UNRESERVED_KEYWORD)
+ PG_KEYWORD("pg_exception_context", K_EXCEPTION_CONTEXT, UNRESERVED_KEYWORD)
+ PG_KEYWORD("pg_exception_detail", K_EXCEPTION_DETAIL, UNRESERVED_KEYWORD)
+ PG_KEYWORD("pg_exception_hint", K_EXCEPTION_HINT, UNRESERVED_KEYWORD)
PG_KEYWORD("prior", K_PRIOR, UNRESERVED_KEYWORD)
PG_KEYWORD("query", K_QUERY, UNRESERVED_KEYWORD)
PG_KEYWORD("relative", K_RELATIVE, UNRESERVED_KEYWORD)
PG_KEYWORD("result_oid", K_RESULT_OID, UNRESERVED_KEYWORD)
+ PG_KEYWORD("returned_sqlstate", K_RETURNED_SQLSTATE, UNRESERVED_KEYWORD)
PG_KEYWORD("reverse", K_REVERSE, UNRESERVED_KEYWORD)
PG_KEYWORD("row_count", K_ROW_COUNT, UNRESERVED_KEYWORD)
PG_KEYWORD("rowtype", K_ROWTYPE, UNRESERVED_KEYWORD)
PG_KEYWORD("scroll", K_SCROLL, UNRESERVED_KEYWORD)
PG_KEYWORD("slice", K_SLICE, UNRESERVED_KEYWORD)
PG_KEYWORD("sqlstate", K_SQLSTATE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("stacked", K_STACKED, UNRESERVED_KEYWORD)
PG_KEYWORD("type", K_TYPE, UNRESERVED_KEYWORD)
PG_KEYWORD("use_column", K_USE_COLUMN, UNRESERVED_KEYWORD)
PG_KEYWORD("use_variable", K_USE_VARIABLE, UNRESERVED_KEYWORD)
*** ./src/test/regress/expected/plpgsql.out.orig 2011-07-07 09:05:08.001657615 +0200
--- ./src/test/regress/expected/plpgsql.out 2011-07-07 09:08:55.533687156 +0200
***************
*** 4434,4436 ****
--- 4434,4511 ----
drop function foreach_test(anyarray);
drop type xy_tuple;
+ -- access to exception data
+ create or replace function bad_function()
+ returns int as $$
+ declare v int := 0;
+ begin
+ return 10 / v;
+ end;
+ $$ language plpgsql;
+ create or replace function raise_function()
+ returns void as $$
+ begin
+ raise exception 'custom exception'
+ using detail = 'some detail of custom exception',
+ hint = 'some hint related to custom exception';
+ end;
+ $$ language plpgsql;
+ create or replace function stacked_diagnostics_test01()
+ returns void as $$
+ declare _sqlstate text; _message text; _context text;
+ begin
+ perform bad_function();
+ exception when others then
+ get stacked diagnostics
+ _sqlstate = returned_sqlstate,
+ _message = message_text,
+ _context = pg_exception_context;
+ raise notice 'sqlstate: %, message: %, context: [%]', _sqlstate, _message, replace(_context, e'\n', ' <- ');
+ end;
+ $$ language plpgsql;
+ select stacked_diagnostics_test01();
+ NOTICE: sqlstate: 22012, message: division by zero, context: [PL/pgSQL function "bad_function" line 4 at RETURN <- SQL statement "SELECT bad_function()" <- PL/pgSQL function "stacked_diagnostics_test01" line 4 at PERFORM]
+ stacked_diagnostics_test01
+ ----------------------------
+
+ (1 row)
+
+ create or replace function stacked_diagnostics_test02()
+ returns void as $$
+ declare _detail text; _hint text; _message text;
+ begin
+ perform raise_function();
+ exception when others then
+ get stacked diagnostics
+ _message = message_text,
+ _detail = pg_exception_detail,
+ _hint = pg_exception_hint;
+ raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
+ end;
+ $$ language plpgsql;
+ select stacked_diagnostics_test02();
+ NOTICE: message: custom exception, detail: some detail of custom exception, hint: some hint related to custom exception
+ stacked_diagnostics_test02
+ ----------------------------
+
+ (1 row)
+
+ -- should to fail, cannot use stacked diagnostics statement outside handler
+ create or replace function stacked_diagnostics_test02()
+ returns void as $$
+ declare _detail text; _hint text; _message text;
+ begin
+ get stacked diagnostics
+ _message = message_text,
+ _detail = pg_exception_detail,
+ _hint = pg_exception_hint;
+ raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
+ end;
+ $$ language plpgsql;
+ select stacked_diagnostics_test02();
+ ERROR: stacked diagnostics accessed without active handler
+ CONTEXT: PL/pgSQL function "stacked_diagnostics_test02" line 4 at GET DIAGNOSTICS
+ drop function bad_function();
+ drop function raise_function();
+ drop function stacked_diagnostics_test01();
+ drop function stacked_diagnostics_test02();
*** ./src/test/regress/sql/plpgsql.sql.orig 2011-07-07 09:05:58.954225738 +0200
--- ./src/test/regress/sql/plpgsql.sql 2011-07-07 09:08:55.535687138 +0200
***************
*** 3489,3491 ****
--- 3489,3563 ----
drop function foreach_test(anyarray);
drop type xy_tuple;
+
+
+ -- access to exception data
+ create or replace function bad_function()
+ returns int as $$
+ declare v int := 0;
+ begin
+ return 10 / v;
+ end;
+ $$ language plpgsql;
+
+ create or replace function raise_function()
+ returns void as $$
+ begin
+ raise exception 'custom exception'
+ using detail = 'some detail of custom exception',
+ hint = 'some hint related to custom exception';
+ end;
+ $$ language plpgsql;
+
+ create or replace function stacked_diagnostics_test01()
+ returns void as $$
+ declare _sqlstate text; _message text; _context text;
+ begin
+ perform bad_function();
+ exception when others then
+ get stacked diagnostics
+ _sqlstate = returned_sqlstate,
+ _message = message_text,
+ _context = pg_exception_context;
+ raise notice 'sqlstate: %, message: %, context: [%]', _sqlstate, _message, replace(_context, e'\n', ' <- ');
+ end;
+ $$ language plpgsql;
+
+ select stacked_diagnostics_test01();
+
+ create or replace function stacked_diagnostics_test02()
+ returns void as $$
+ declare _detail text; _hint text; _message text;
+ begin
+ perform raise_function();
+ exception when others then
+ get stacked diagnostics
+ _message = message_text,
+ _detail = pg_exception_detail,
+ _hint = pg_exception_hint;
+ raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
+ end;
+ $$ language plpgsql;
+
+ select stacked_diagnostics_test02();
+
+ -- should to fail, cannot use stacked diagnostics statement outside handler
+
+ create or replace function stacked_diagnostics_test02()
+ returns void as $$
+ declare _detail text; _hint text; _message text;
+ begin
+ get stacked diagnostics
+ _message = message_text,
+ _detail = pg_exception_detail,
+ _hint = pg_exception_hint;
+ raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
+ end;
+ $$ language plpgsql;
+
+ select stacked_diagnostics_test02();
+
+ drop function bad_function();
+ drop function raise_function();
+ drop function stacked_diagnostics_test01();
+ drop function stacked_diagnostics_test02();
Re: patch: enhanced get diagnostics statement 2
On Jul 7, 2011, at 12:30 AM, Pavel Stehule wrote:
thank you very much for review.
I thank you, too, Hanada-san. I was assigned to review this patch, but you beat me to it. So now I'll do the follow-up review.
I cleaned patch and merged your documentation patch
I hope, this is all - a language correction should do some native speaker
Contents & Purpose
==================
The patch extends the `GET DIAGNOSTICS` syntax to accept new two new keywords, `CURRENT` and `STACKED`, which are described in the SQL/PSM standard. This feature allows one to retrieve exception information in an `EXCEPTION` block.
The patch also adds three PostgreSQL-specific fields:
* `PG_EXCEPTION_DETAIL` contains the exception detail message
* `PG_EXCEPTION_HINT` contains the exception hint, if any
* `PG_EXCEPTION_CONTEXT` contains lines that describes call stack
Submission Review
=================
The patch is a unified diff, and applies cleanly to master at 89fd72cb. The regression tests all pass successfully against the new patch, so the test cases are sane and do cover the new behavior.
The patch includes regression tests which appear to adequately cover the proposed functionality. They also contain documentation, although the wording, while understandable, needs the attention of a native speaker. I've taken it upon myself to make some revisions, including moving the list of fields into a list. I've attached a new patch with these changes below.
Usability review
================
* I agree that it's useful to get detailed information inside EXCEPTION clause.
* We don't have this feature yet.
* This patch follows SQL spec of GET DIAGNOSTICS, and extends about PG-specific variables.
* pg_dump support is not required for this feature.
* AFAICS, this patch doesn't have any danger, such as breakage of backward compatibility, thanks to the new `STACKED` keyword. I suppose there could be a conflict if someone had a variable named STACKED in the function, but I doubt it, given the context in which it's used.
Feature test
============
* The new feature introduced by the patch works well. I ran some basic tests and it worked very nicely. I'm excited to get this functionality!
Conclusion
==========
Attached is a new patch with my documentation changes (and in context diff format). The code looks clean and unobtrusive, the functionality it adds is useful, and overall I'd say it's ready for committer.
Best,
David
Attachments:
stacked_diagnostics_3.patchapplication/octet-stream; name=stacked_diagnostics_3.patch; x-unix-mode=0644Download
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 20bc991..b8e594d 100644
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*************** EXECUTE format('UPDATE tbl SET %I = $1 W
*** 1387,1393 ****
command, which has the form:
<synopsis>
! GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
</synopsis>
This command allows retrieval of system status indicators. Each
--- 1387,1393 ----
command, which has the form:
<synopsis>
! GET <optional> CURRENT </optional> DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
</synopsis>
This command allows retrieval of system status indicators. Each
*************** GET DIAGNOSTICS integer_var = ROW_COUNT;
*** 1488,1493 ****
--- 1488,1566 ----
</sect2>
+ <sect2 id="plpgsql-exception-diagnostics">
+ <title>Obtaining the Exception Status</title>
+
+ <para>
+ Inside an exception handler, one may retrieve detailed
+ information about the current exception using THE
+ <command>GET STACKED DIAGNOSTICS</command> command, which has the form:
+
+ <synopsis>
+ GET STACKED DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
+ </synopsis>
+ </para>
+
+ <para>
+ This command allows retrieval of the exception's data. Each
+ <replaceable>item</replaceable> is a key word identifying a state
+ value to be assigned to the specified variable (which should be
+ of the right data type to receive it). The currently available
+ status items are:
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ <varname>RETURNED_SQLSTATE</varname>, the SQLSTATE of the exception
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <varname>MESSAGE_TEXT</varname>, the text of the exception's message
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <varname>PG_EXCEPTION_DETAIL</varname>, the text of the exception's detail message
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <varname>PG_EXCEPTION_HINT</varname>, the text of the exception's hint message
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <varname>PG_EXCEPTION_CONTEXT</varname>, lines of text describing the call stack
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ If an exception does not contain a value for an item, an empty string
+ will be returned.
+ </para>
+
+ <para>
+ An example:
+ <programlisting>
+ DECLARE
+ text_var1 text;
+ text_var2 text;
+ text_var3 text;
+ BEGIN
+ -- some processing which might cause an exception
+ ...
+ EXCEPTION WHEN OTHERS THEN
+ GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
+ text_var2 = PG_EXCEPTION_DETAIL,
+ text_var3 = PG_EXCEPTION_HINT;
+ END;
+ </programlisting>
+ </para>
+
+
+ </sect2>
+
<sect2 id="plpgsql-statements-null">
<title>Doing Nothing At All</title>
diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt
index 0315f6b..bce950e 100644
*** a/src/backend/utils/errcodes.txt
--- b/src/backend/utils/errcodes.txt
*************** Section: Class 0P - Invalid Role Specifi
*** 132,137 ****
--- 132,140 ----
0P000 E ERRCODE_INVALID_ROLE_SPECIFICATION invalid_role_specification
+ Section: Class 0Z - Diagnostics Exception
+ 0Z002 E ERRCODE_STACKED_DIAGNOSTICS_ACCESSED_WITHOUT_ACTIVE_HANDLER stacked_diagnostics_accessed_without_active_handler
+
Section: Class 20 - Case Not Found
20000 E ERRCODE_CASE_NOT_FOUND case_not_found
diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y
index 4e2b705..e6c19a1 100644
*** a/src/pl/plpgsql/src/gram.y
--- b/src/pl/plpgsql/src/gram.y
*************** static List *read_raise_options(void);
*** 206,211 ****
--- 206,212 ----
%type <list> getdiag_list
%type <diagitem> getdiag_list_item
%type <ival> getdiag_item getdiag_target
+ %type <boolean> getdiag_opt
%type <ival> opt_scrollable
%type <fetch> opt_fetch_direction
*************** static List *read_raise_options(void);
*** 250,256 ****
--- 251,259 ----
%token <keyword> K_CLOSE
%token <keyword> K_COLLATE
%token <keyword> K_CONSTANT
+ %token <keyword> K_CONTEXT
%token <keyword> K_CONTINUE
+ %token <keyword> K_CURRENT
%token <keyword> K_CURSOR
%token <keyword> K_DEBUG
%token <keyword> K_DECLARE
*************** static List *read_raise_options(void);
*** 263,268 ****
--- 266,274 ----
%token <keyword> K_END
%token <keyword> K_ERRCODE
%token <keyword> K_ERROR
+ %token <keyword> K_EXCEPTION_CONTEXT
+ %token <keyword> K_EXCEPTION_DETAIL
+ %token <keyword> K_EXCEPTION_HINT
%token <keyword> K_EXCEPTION
%token <keyword> K_EXECUTE
%token <keyword> K_EXIT
*************** static List *read_raise_options(void);
*** 284,289 ****
--- 290,296 ----
%token <keyword> K_LOG
%token <keyword> K_LOOP
%token <keyword> K_MESSAGE
+ %token <keyword> K_MESSAGE_TEXT
%token <keyword> K_MOVE
%token <keyword> K_NEXT
%token <keyword> K_NO
*************** static List *read_raise_options(void);
*** 300,311 ****
--- 307,320 ----
%token <keyword> K_RELATIVE
%token <keyword> K_RESULT_OID
%token <keyword> K_RETURN
+ %token <keyword> K_RETURNED_SQLSTATE
%token <keyword> K_REVERSE
%token <keyword> K_ROWTYPE
%token <keyword> K_ROW_COUNT
%token <keyword> K_SCROLL
%token <keyword> K_SLICE
%token <keyword> K_SQLSTATE
+ %token <keyword> K_STACKED
%token <keyword> K_STRICT
%token <keyword> K_THEN
%token <keyword> K_TO
*************** stmt_assign : assign_var assign_operato
*** 832,845 ****
}
;
! stmt_getdiag : K_GET K_DIAGNOSTICS getdiag_list ';'
{
PLpgSQL_stmt_getdiag *new;
new = palloc0(sizeof(PLpgSQL_stmt_getdiag));
new->cmd_type = PLPGSQL_STMT_GETDIAG;
new->lineno = plpgsql_location_to_lineno(@1);
! new->diag_items = $3;
$$ = (PLpgSQL_stmt *)new;
}
--- 841,892 ----
}
;
! stmt_getdiag : K_GET getdiag_opt K_DIAGNOSTICS getdiag_list ';'
{
PLpgSQL_stmt_getdiag *new;
+ ListCell *lc;
new = palloc0(sizeof(PLpgSQL_stmt_getdiag));
new->cmd_type = PLPGSQL_STMT_GETDIAG;
new->lineno = plpgsql_location_to_lineno(@1);
! new->is_stacked = $2;
! new->diag_items = $4;
!
! /*
! * stacked diagnostics statements allows only access
! * to exception data fields. whereas current diagnostics
! * statement disallow access to exception data fields.
! */
! foreach(lc, $4)
! {
! PLpgSQL_diag_item *ditem = (PLpgSQL_diag_item *) lfirst(lc);
!
! switch (ditem->kind)
! {
! /* these fields are disallowed in stacked diagnostics statement */
! case PLPGSQL_GETDIAG_ROW_COUNT:
! case PLPGSQL_GETDIAG_RESULT_OID:
! if ($2)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("cannot use ROW_COUNT or RESULT_OID inside stacked diagnostics statemet"),
! parser_errposition(@1)));
! break;
!
! /* these fields are disallowed in current diagnostics statement */
! case PLPGSQL_GETDIAG_ERROR_CONTEXT:
! case PLPGSQL_GETDIAG_ERROR_DETAIL:
! case PLPGSQL_GETDIAG_ERROR_HINT:
! case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
! case PLPGSQL_GETDIAG_MESSAGE_TEXT:
! if (!$2)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("EXCEPTION_CONTEXT or EXCEPTION_DETAIL or EXCEPTION_HINT or RETURNED_SQLSTATE or MESSAGE_TEXT are not allowed in current diagnostics statement"),
! parser_errposition(@1)));
!
! }
! }
$$ = (PLpgSQL_stmt *)new;
}
*************** getdiag_list_item : getdiag_target assig
*** 867,872 ****
--- 914,933 ----
}
;
+ getdiag_opt :
+ {
+ $$ = false;
+ }
+ | K_CURRENT
+ {
+ $$ = false;
+ }
+ | K_STACKED
+ {
+ $$ = true;
+ }
+ ;
+
getdiag_item :
{
int tok = yylex();
*************** getdiag_item :
*** 877,882 ****
--- 938,958 ----
else if (tok_is_keyword(tok, &yylval,
K_RESULT_OID, "result_oid"))
$$ = PLPGSQL_GETDIAG_RESULT_OID;
+ else if (tok_is_keyword(tok, &yylval,
+ K_EXCEPTION_DETAIL, "pg_exception_detail"))
+ $$ = PLPGSQL_GETDIAG_ERROR_DETAIL;
+ else if (tok_is_keyword(tok, &yylval,
+ K_EXCEPTION_HINT, "pg_exception_hint"))
+ $$ = PLPGSQL_GETDIAG_ERROR_HINT;
+ else if (tok_is_keyword(tok, &yylval,
+ K_EXCEPTION_CONTEXT, "pg_exception_context"))
+ $$ = PLPGSQL_GETDIAG_ERROR_CONTEXT;
+ else if (tok_is_keyword(tok, &yylval,
+ K_MESSAGE_TEXT, "message_text"))
+ $$ = PLPGSQL_GETDIAG_MESSAGE_TEXT;
+ else if (tok_is_keyword(tok, &yylval,
+ K_RETURNED_SQLSTATE, "returned_sqlstate"))
+ $$ = PLPGSQL_GETDIAG_RETURNED_SQLSTATE;
else
yyerror("unrecognized GET DIAGNOSTICS item");
}
*************** unreserved_keyword :
*** 2135,2146 ****
--- 2211,2226 ----
| K_ARRAY
| K_BACKWARD
| K_CONSTANT
+ | K_CURRENT
| K_CURSOR
| K_DEBUG
| K_DETAIL
| K_DUMP
| K_ERRCODE
| K_ERROR
+ | K_EXCEPTION_CONTEXT
+ | K_EXCEPTION_DETAIL
+ | K_EXCEPTION_HINT
| K_FIRST
| K_FORWARD
| K_HINT
*************** unreserved_keyword :
*** 2149,2154 ****
--- 2229,2235 ----
| K_LAST
| K_LOG
| K_MESSAGE
+ | K_MESSAGE_TEXT
| K_NEXT
| K_NO
| K_NOTICE
*************** unreserved_keyword :
*** 2157,2168 ****
--- 2238,2251 ----
| K_QUERY
| K_RELATIVE
| K_RESULT_OID
+ | K_RETURNED_SQLSTATE
| K_REVERSE
| K_ROW_COUNT
| K_ROWTYPE
| K_SCROLL
| K_SLICE
| K_SQLSTATE
+ | K_STACKED
| K_TYPE
| K_USE_COLUMN
| K_USE_VARIABLE
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 7f948d5..312ae2f 100644
*** a/src/pl/plpgsql/src/pl_exec.c
--- b/src/pl/plpgsql/src/pl_exec.c
*************** exec_stmt_getdiag(PLpgSQL_execstate *est
*** 1450,1455 ****
--- 1450,1507 ----
ObjectIdGetDatum(estate->eval_lastoid),
OIDOID, &isnull);
break;
+ case PLPGSQL_GETDIAG_ERROR_DETAIL:
+ case PLPGSQL_GETDIAG_ERROR_HINT:
+ case PLPGSQL_GETDIAG_ERROR_CONTEXT:
+ case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
+ case PLPGSQL_GETDIAG_MESSAGE_TEXT:
+ {
+ char *strval = NULL;
+ Datum value;
+
+ /*
+ * Now a fields based on processing of Error Data
+ * are handled.
+ */
+ if (estate->cur_error == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_STACKED_DIAGNOSTICS_ACCESSED_WITHOUT_ACTIVE_HANDLER),
+ errmsg("stacked diagnostics accessed without active handler")));
+ switch (diag_item->kind)
+ {
+ case PLPGSQL_GETDIAG_ERROR_DETAIL:
+ strval = estate->cur_error->detail;
+ break;
+ case PLPGSQL_GETDIAG_ERROR_HINT:
+ strval = estate->cur_error->hint;
+ break;
+ case PLPGSQL_GETDIAG_ERROR_CONTEXT:
+ strval = estate->cur_error->context;
+ break;
+ case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
+ strval = unpack_sql_state(estate->cur_error->sqlerrcode);
+ break;
+ case PLPGSQL_GETDIAG_MESSAGE_TEXT:
+ strval = estate->cur_error->message;
+ }
+
+ if (strval != NULL)
+ {
+ value = PointerGetDatum(cstring_to_text(strval));
+ }
+ else
+ {
+ /*
+ * Diagnostics fields are never NULL, it should to return empty string
+ * instead.
+ */
+ value = PointerGetDatum(cstring_to_text(""));
+ }
+
+ exec_assign_value(estate, var,
+ value, TEXTOID, &isnull);
+ break;
+ }
default:
elog(ERROR, "unrecognized attribute request: %d",
diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c
index 1f83114..ba90b0d 100644
*** a/src/pl/plpgsql/src/pl_funcs.c
--- b/src/pl/plpgsql/src/pl_funcs.c
*************** dump_getdiag(PLpgSQL_stmt_getdiag *stmt)
*** 1389,1395 ****
ListCell *lc;
dump_ind();
! printf("GET DIAGNOSTICS ");
foreach(lc, stmt->diag_items)
{
PLpgSQL_diag_item *diag_item = (PLpgSQL_diag_item *) lfirst(lc);
--- 1389,1395 ----
ListCell *lc;
dump_ind();
! printf("GET %s DIAGNOSTICS ", stmt->is_stacked ? "STACKED" : "CURRENT");
foreach(lc, stmt->diag_items)
{
PLpgSQL_diag_item *diag_item = (PLpgSQL_diag_item *) lfirst(lc);
*************** dump_getdiag(PLpgSQL_stmt_getdiag *stmt)
*** 1409,1414 ****
--- 1409,1434 ----
printf("RESULT_OID");
break;
+ case PLPGSQL_GETDIAG_ERROR_CONTEXT:
+ printf("PG_EXCEPTION_CONTEXT");
+ break;
+
+ case PLPGSQL_GETDIAG_ERROR_DETAIL:
+ printf("PG_EXCEPTION_DETAIL");
+ break;
+
+ case PLPGSQL_GETDIAG_ERROR_HINT:
+ printf("PG_EXCEPTION_HINT");
+ break;
+
+ case PLPGSQL_GETDIAG_MESSAGE_TEXT:
+ printf("MESSAGE_TEXT");
+ break;
+
+ case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
+ printf("RETURNED_SQLSTATE");
+ break;
+
default:
printf("???");
break;
diff --git a/src/pl/plpgsql/src/pl_scanner.c b/src/pl/plpgsql/src/pl_scanner.c
index e1c0b62..2bde565 100644
*** a/src/pl/plpgsql/src/pl_scanner.c
--- b/src/pl/plpgsql/src/pl_scanner.c
*************** static const ScanKeyword unreserved_keyw
*** 110,115 ****
--- 110,116 ----
PG_KEYWORD("array", K_ARRAY, UNRESERVED_KEYWORD)
PG_KEYWORD("backward", K_BACKWARD, UNRESERVED_KEYWORD)
PG_KEYWORD("constant", K_CONSTANT, UNRESERVED_KEYWORD)
+ PG_KEYWORD("current", K_CURRENT, UNRESERVED_KEYWORD)
PG_KEYWORD("cursor", K_CURSOR, UNRESERVED_KEYWORD)
PG_KEYWORD("debug", K_DEBUG, UNRESERVED_KEYWORD)
PG_KEYWORD("detail", K_DETAIL, UNRESERVED_KEYWORD)
*************** static const ScanKeyword unreserved_keyw
*** 124,143 ****
--- 125,150 ----
PG_KEYWORD("last", K_LAST, UNRESERVED_KEYWORD)
PG_KEYWORD("log", K_LOG, UNRESERVED_KEYWORD)
PG_KEYWORD("message", K_MESSAGE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("message_text", K_MESSAGE_TEXT, UNRESERVED_KEYWORD)
PG_KEYWORD("next", K_NEXT, UNRESERVED_KEYWORD)
PG_KEYWORD("no", K_NO, UNRESERVED_KEYWORD)
PG_KEYWORD("notice", K_NOTICE, UNRESERVED_KEYWORD)
PG_KEYWORD("option", K_OPTION, UNRESERVED_KEYWORD)
+ PG_KEYWORD("pg_exception_context", K_EXCEPTION_CONTEXT, UNRESERVED_KEYWORD)
+ PG_KEYWORD("pg_exception_detail", K_EXCEPTION_DETAIL, UNRESERVED_KEYWORD)
+ PG_KEYWORD("pg_exception_hint", K_EXCEPTION_HINT, UNRESERVED_KEYWORD)
PG_KEYWORD("prior", K_PRIOR, UNRESERVED_KEYWORD)
PG_KEYWORD("query", K_QUERY, UNRESERVED_KEYWORD)
PG_KEYWORD("relative", K_RELATIVE, UNRESERVED_KEYWORD)
PG_KEYWORD("result_oid", K_RESULT_OID, UNRESERVED_KEYWORD)
+ PG_KEYWORD("returned_sqlstate", K_RETURNED_SQLSTATE, UNRESERVED_KEYWORD)
PG_KEYWORD("reverse", K_REVERSE, UNRESERVED_KEYWORD)
PG_KEYWORD("row_count", K_ROW_COUNT, UNRESERVED_KEYWORD)
PG_KEYWORD("rowtype", K_ROWTYPE, UNRESERVED_KEYWORD)
PG_KEYWORD("scroll", K_SCROLL, UNRESERVED_KEYWORD)
PG_KEYWORD("slice", K_SLICE, UNRESERVED_KEYWORD)
PG_KEYWORD("sqlstate", K_SQLSTATE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("stacked", K_STACKED, UNRESERVED_KEYWORD)
PG_KEYWORD("type", K_TYPE, UNRESERVED_KEYWORD)
PG_KEYWORD("use_column", K_USE_COLUMN, UNRESERVED_KEYWORD)
PG_KEYWORD("use_variable", K_USE_VARIABLE, UNRESERVED_KEYWORD)
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index 89103ae..96d04cb 100644
*** a/src/pl/plpgsql/src/plpgsql.h
--- b/src/pl/plpgsql/src/plpgsql.h
*************** enum
*** 126,132 ****
enum
{
PLPGSQL_GETDIAG_ROW_COUNT,
! PLPGSQL_GETDIAG_RESULT_OID
};
/* --------
--- 126,137 ----
enum
{
PLPGSQL_GETDIAG_ROW_COUNT,
! PLPGSQL_GETDIAG_RESULT_OID,
! PLPGSQL_GETDIAG_ERROR_CONTEXT,
! PLPGSQL_GETDIAG_ERROR_DETAIL,
! PLPGSQL_GETDIAG_ERROR_HINT,
! PLPGSQL_GETDIAG_RETURNED_SQLSTATE,
! PLPGSQL_GETDIAG_MESSAGE_TEXT
};
/* --------
*************** typedef struct
*** 377,382 ****
--- 382,388 ----
int cmd_type;
int lineno;
List *diag_items; /* List of PLpgSQL_diag_item */
+ bool is_stacked; /* true when it access a second area of diagnostics info */
} PLpgSQL_stmt_getdiag;
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index bfabcbc..8b60d34 100644
*** a/src/test/regress/expected/plpgsql.out
--- b/src/test/regress/expected/plpgsql.out
*************** NOTICE: {"(35,78)","(88,76)"}
*** 4434,4436 ****
--- 4434,4511 ----
drop function foreach_test(anyarray);
drop type xy_tuple;
+ -- access to exception data
+ create or replace function bad_function()
+ returns int as $$
+ declare v int := 0;
+ begin
+ return 10 / v;
+ end;
+ $$ language plpgsql;
+ create or replace function raise_function()
+ returns void as $$
+ begin
+ raise exception 'custom exception'
+ using detail = 'some detail of custom exception',
+ hint = 'some hint related to custom exception';
+ end;
+ $$ language plpgsql;
+ create or replace function stacked_diagnostics_test01()
+ returns void as $$
+ declare _sqlstate text; _message text; _context text;
+ begin
+ perform bad_function();
+ exception when others then
+ get stacked diagnostics
+ _sqlstate = returned_sqlstate,
+ _message = message_text,
+ _context = pg_exception_context;
+ raise notice 'sqlstate: %, message: %, context: [%]', _sqlstate, _message, replace(_context, e'\n', ' <- ');
+ end;
+ $$ language plpgsql;
+ select stacked_diagnostics_test01();
+ NOTICE: sqlstate: 22012, message: division by zero, context: [PL/pgSQL function "bad_function" line 4 at RETURN <- SQL statement "SELECT bad_function()" <- PL/pgSQL function "stacked_diagnostics_test01" line 4 at PERFORM]
+ stacked_diagnostics_test01
+ ----------------------------
+
+ (1 row)
+
+ create or replace function stacked_diagnostics_test02()
+ returns void as $$
+ declare _detail text; _hint text; _message text;
+ begin
+ perform raise_function();
+ exception when others then
+ get stacked diagnostics
+ _message = message_text,
+ _detail = pg_exception_detail,
+ _hint = pg_exception_hint;
+ raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
+ end;
+ $$ language plpgsql;
+ select stacked_diagnostics_test02();
+ NOTICE: message: custom exception, detail: some detail of custom exception, hint: some hint related to custom exception
+ stacked_diagnostics_test02
+ ----------------------------
+
+ (1 row)
+
+ -- should to fail, cannot use stacked diagnostics statement outside handler
+ create or replace function stacked_diagnostics_test02()
+ returns void as $$
+ declare _detail text; _hint text; _message text;
+ begin
+ get stacked diagnostics
+ _message = message_text,
+ _detail = pg_exception_detail,
+ _hint = pg_exception_hint;
+ raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
+ end;
+ $$ language plpgsql;
+ select stacked_diagnostics_test02();
+ ERROR: stacked diagnostics accessed without active handler
+ CONTEXT: PL/pgSQL function "stacked_diagnostics_test02" line 4 at GET DIAGNOSTICS
+ drop function bad_function();
+ drop function raise_function();
+ drop function stacked_diagnostics_test01();
+ drop function stacked_diagnostics_test02();
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 14fb457..658bea7 100644
*** a/src/test/regress/sql/plpgsql.sql
--- b/src/test/regress/sql/plpgsql.sql
*************** select foreach_test(ARRAY[[(10,20),(40,6
*** 3489,3491 ****
--- 3489,3563 ----
drop function foreach_test(anyarray);
drop type xy_tuple;
+
+
+ -- access to exception data
+ create or replace function bad_function()
+ returns int as $$
+ declare v int := 0;
+ begin
+ return 10 / v;
+ end;
+ $$ language plpgsql;
+
+ create or replace function raise_function()
+ returns void as $$
+ begin
+ raise exception 'custom exception'
+ using detail = 'some detail of custom exception',
+ hint = 'some hint related to custom exception';
+ end;
+ $$ language plpgsql;
+
+ create or replace function stacked_diagnostics_test01()
+ returns void as $$
+ declare _sqlstate text; _message text; _context text;
+ begin
+ perform bad_function();
+ exception when others then
+ get stacked diagnostics
+ _sqlstate = returned_sqlstate,
+ _message = message_text,
+ _context = pg_exception_context;
+ raise notice 'sqlstate: %, message: %, context: [%]', _sqlstate, _message, replace(_context, e'\n', ' <- ');
+ end;
+ $$ language plpgsql;
+
+ select stacked_diagnostics_test01();
+
+ create or replace function stacked_diagnostics_test02()
+ returns void as $$
+ declare _detail text; _hint text; _message text;
+ begin
+ perform raise_function();
+ exception when others then
+ get stacked diagnostics
+ _message = message_text,
+ _detail = pg_exception_detail,
+ _hint = pg_exception_hint;
+ raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
+ end;
+ $$ language plpgsql;
+
+ select stacked_diagnostics_test02();
+
+ -- should to fail, cannot use stacked diagnostics statement outside handler
+
+ create or replace function stacked_diagnostics_test02()
+ returns void as $$
+ declare _detail text; _hint text; _message text;
+ begin
+ get stacked diagnostics
+ _message = message_text,
+ _detail = pg_exception_detail,
+ _hint = pg_exception_hint;
+ raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
+ end;
+ $$ language plpgsql;
+
+ select stacked_diagnostics_test02();
+
+ drop function bad_function();
+ drop function raise_function();
+ drop function stacked_diagnostics_test01();
+ drop function stacked_diagnostics_test02();
Re: patch: enhanced get diagnostics statement 2
A couple items for this patch:
The docs state that the variable to receive each diagnostic value needs
to be "of the right data type" but fails to specify what it is. I think
it'd be good to turn that <itemizedlist> into a table with three
columns: name, type, description.
This seems poor style:
+ case PLPGSQL_GETDIAG_ERROR_CONTEXT:
+ case PLPGSQL_GETDIAG_ERROR_DETAIL:
+ case PLPGSQL_GETDIAG_ERROR_HINT:
+ case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
+ case PLPGSQL_GETDIAG_MESSAGE_TEXT:
+ if (!$2)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("EXCEPTION_CONTEXT or EXCEPTION_DETAIL or EXCEPTION_HINT or RETURNED_SQLSTATE or MESSAGE_TEXT are not allowed in current diagnostics statement"),
+ parser_errposition(@1)));
+ I think we could replace this with something like
+ if (!$2)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("diagnostic value %s is not allowed in GET CURRENT DIAGNOSTICS statement", stringify(ditem->kind)),Other than that, and a few grammar fixes in code comments, this seems
good to me.
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: patch: enhanced get diagnostics statement 2
2011/7/14 Alvaro Herrera <alvherre@commandprompt.com>:
A couple items for this patch:
The docs state that the variable to receive each diagnostic value needs
to be "of the right data type" but fails to specify what it is. I think
it'd be good to turn that <itemizedlist> into a table with three
columns: name, type, description.This seems poor style:
+ case PLPGSQL_GETDIAG_ERROR_CONTEXT: + case PLPGSQL_GETDIAG_ERROR_DETAIL: + case PLPGSQL_GETDIAG_ERROR_HINT: + case PLPGSQL_GETDIAG_RETURNED_SQLSTATE: + case PLPGSQL_GETDIAG_MESSAGE_TEXT: + if (!$2) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("EXCEPTION_CONTEXT or EXCEPTION_DETAIL or EXCEPTION_HINT or RETURNED_SQLSTATE or MESSAGE_TEXT are not allowed in current diagnostics statement"), + parser_errposition(@1))); +I think we could replace this with something like
+ if (!$2) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("diagnostic value %s is not allowed in GET CURRENT DIAGNOSTICS statement", stringify(ditem->kind)),Other than that, and a few grammar fixes in code comments, this seems
good to me.
it is good idea
Regards
Pavel
Show quoted text
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: patch: enhanced get diagnostics statement 2
Excerpts from Pavel Stehule's message of jue jul 14 16:25:56 -0400 2011:
2011/7/14 Alvaro Herrera <alvherre@commandprompt.com>:
A couple items for this patch:
it is good idea
Thanks ... I expect you're going to resubmit the patch based on David's
last version and my comments?
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: patch: enhanced get diagnostics statement 2
2011/7/14 Alvaro Herrera <alvherre@commandprompt.com>:
Excerpts from Pavel Stehule's message of jue jul 14 16:25:56 -0400 2011:
2011/7/14 Alvaro Herrera <alvherre@commandprompt.com>:
A couple items for this patch:
it is good idea
Thanks ... I expect you're going to resubmit the patch based on David's
last version and my comments?
yes, but tomorrow, time to go sleep
Regards
Pavel
Show quoted text
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: patch: enhanced get diagnostics statement 2
Hello
2011/7/14 Alvaro Herrera <alvherre@commandprompt.com>:
Excerpts from Pavel Stehule's message of jue jul 14 16:25:56 -0400 2011:
2011/7/14 Alvaro Herrera <alvherre@commandprompt.com>:
A couple items for this patch:
it is good idea
Thanks ... I expect you're going to resubmit the patch based on David's
last version and my comments?
yes, see a attachment
Regards
Pavel
Show quoted text
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Attachments:
stacked_diagnostics.difftext/x-patch; charset=US-ASCII; name=stacked_diagnostics.diffDownload
*** ./doc/src/sgml/plpgsql.sgml.orig 2011-07-15 07:53:03.069787671 +0200
--- ./doc/src/sgml/plpgsql.sgml 2011-07-15 08:36:00.504591377 +0200
***************
*** 1387,1393 ****
command, which has the form:
<synopsis>
! GET DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
</synopsis>
This command allows retrieval of system status indicators. Each
--- 1387,1393 ----
command, which has the form:
<synopsis>
! GET <optional> CURRENT </optional> DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
</synopsis>
This command allows retrieval of system status indicators. Each
***************
*** 1488,1493 ****
--- 1488,1580 ----
</sect2>
+ <sect2 id="plpgsql-exception-diagnostics">
+ <title>Obtaining the Exception Status</title>
+
+ <para>
+ Inside an exception handler, one may retrieve detailed
+ information about the current exception using THE
+ <command>GET STACKED DIAGNOSTICS</command> command, which has the form:
+
+ <synopsis>
+ GET STACKED DIAGNOSTICS <replaceable>variable</replaceable> = <replaceable>item</replaceable> <optional> , ... </optional>;
+ </synopsis>
+ </para>
+
+ <para>
+ This command allows retrieval of the exception's data. Each
+ <replaceable>item</replaceable> is a key word identifying a state
+ value to be assigned to the specified variable (which should be
+ of the right data type to receive it). The currently available
+ status items are:
+
+ <table id="plpgsql-exception-diagnostics-values">
+ <title>Stacked diagnostics values</title>
+ <tgroup cols="3">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Return type</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>RETURNED_SQLSTATE</entry>
+ <entry>text</entry>
+ <entry>the SQLSTATE of the exception</entry>
+ </row>
+ <row>
+ <entry>MESSAGE_TEXT</entry>
+ <entry>text</entry>
+ <entry>the text of the exception's message</entry>
+ </row>
+ <row>
+ <entry>PG_EXCEPTION_DETAIL</entry>
+ <entry>text</entry>
+ <entry>the text of the exception's detail message</entry>
+ </row>
+ <row>
+ <entry>PG_EXCEPTION_HINT</entry>
+ <entry>text</entry>
+ <entry>the text of the exception's hint message</entry>
+ </row>
+ <row>
+ <entry>PG_EXCEPTION_CONTEXT</entry>
+ <entry>text</entry>
+ <entry>lines of text describing the call stack</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </para>
+
+ <para>
+ If an exception does not contain a value for an item, an empty string
+ will be returned.
+ </para>
+
+ <para>
+ An example:
+ <programlisting>
+ DECLARE
+ text_var1 text;
+ text_var2 text;
+ text_var3 text;
+ BEGIN
+ -- some processing which might cause an exception
+ ...
+ EXCEPTION WHEN OTHERS THEN
+ GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
+ text_var2 = PG_EXCEPTION_DETAIL,
+ text_var3 = PG_EXCEPTION_HINT;
+ END;
+ </programlisting>
+ </para>
+
+
+ </sect2>
+
<sect2 id="plpgsql-statements-null">
<title>Doing Nothing At All</title>
*** ./src/backend/utils/errcodes.txt.orig 2011-07-15 07:53:03.070787661 +0200
--- ./src/backend/utils/errcodes.txt 2011-07-15 08:01:04.522609180 +0200
***************
*** 132,137 ****
--- 132,140 ----
0P000 E ERRCODE_INVALID_ROLE_SPECIFICATION invalid_role_specification
+ Section: Class 0Z - Diagnostics Exception
+ 0Z002 E ERRCODE_STACKED_DIAGNOSTICS_ACCESSED_WITHOUT_ACTIVE_HANDLER stacked_diagnostics_accessed_without_active_handler
+
Section: Class 20 - Case Not Found
20000 E ERRCODE_CASE_NOT_FOUND case_not_found
*** ./src/pl/plpgsql/src/gram.y.orig 2011-07-15 07:53:03.071787651 +0200
--- ./src/pl/plpgsql/src/gram.y 2011-07-15 09:29:27.959407772 +0200
***************
*** 206,211 ****
--- 206,212 ----
%type <list> getdiag_list
%type <diagitem> getdiag_list_item
%type <ival> getdiag_item getdiag_target
+ %type <boolean> getdiag_opt
%type <ival> opt_scrollable
%type <fetch> opt_fetch_direction
***************
*** 250,256 ****
--- 251,259 ----
%token <keyword> K_CLOSE
%token <keyword> K_COLLATE
%token <keyword> K_CONSTANT
+ %token <keyword> K_CONTEXT
%token <keyword> K_CONTINUE
+ %token <keyword> K_CURRENT
%token <keyword> K_CURSOR
%token <keyword> K_DEBUG
%token <keyword> K_DECLARE
***************
*** 263,268 ****
--- 266,274 ----
%token <keyword> K_END
%token <keyword> K_ERRCODE
%token <keyword> K_ERROR
+ %token <keyword> K_EXCEPTION_CONTEXT
+ %token <keyword> K_EXCEPTION_DETAIL
+ %token <keyword> K_EXCEPTION_HINT
%token <keyword> K_EXCEPTION
%token <keyword> K_EXECUTE
%token <keyword> K_EXIT
***************
*** 284,289 ****
--- 290,296 ----
%token <keyword> K_LOG
%token <keyword> K_LOOP
%token <keyword> K_MESSAGE
+ %token <keyword> K_MESSAGE_TEXT
%token <keyword> K_MOVE
%token <keyword> K_NEXT
%token <keyword> K_NO
***************
*** 300,311 ****
--- 307,320 ----
%token <keyword> K_RELATIVE
%token <keyword> K_RESULT_OID
%token <keyword> K_RETURN
+ %token <keyword> K_RETURNED_SQLSTATE
%token <keyword> K_REVERSE
%token <keyword> K_ROWTYPE
%token <keyword> K_ROW_COUNT
%token <keyword> K_SCROLL
%token <keyword> K_SLICE
%token <keyword> K_SQLSTATE
+ %token <keyword> K_STACKED
%token <keyword> K_STRICT
%token <keyword> K_THEN
%token <keyword> K_TO
***************
*** 832,845 ****
}
;
! stmt_getdiag : K_GET K_DIAGNOSTICS getdiag_list ';'
{
PLpgSQL_stmt_getdiag *new;
new = palloc0(sizeof(PLpgSQL_stmt_getdiag));
new->cmd_type = PLPGSQL_STMT_GETDIAG;
new->lineno = plpgsql_location_to_lineno(@1);
! new->diag_items = $3;
$$ = (PLpgSQL_stmt *)new;
}
--- 841,898 ----
}
;
! stmt_getdiag : K_GET getdiag_opt K_DIAGNOSTICS getdiag_list ';'
{
PLpgSQL_stmt_getdiag *new;
+ ListCell *lc;
new = palloc0(sizeof(PLpgSQL_stmt_getdiag));
new->cmd_type = PLPGSQL_STMT_GETDIAG;
new->lineno = plpgsql_location_to_lineno(@1);
! new->is_stacked = $2;
! new->diag_items = $4;
!
! /*
! * stacked diagnostics statements allows only access
! * to exception data fields. whereas current diagnostics
! * statement disallow access to exception data fields.
! */
! foreach(lc, new->diag_items)
! {
! PLpgSQL_diag_item *ditem = (PLpgSQL_diag_item *) lfirst(lc);
!
! if (new->is_stacked)
! {
! switch (ditem->kind)
! {
! /* these fields are disallowed in stacked diagnostics statement */
! case PLPGSQL_GETDIAG_ROW_COUNT:
! case PLPGSQL_GETDIAG_RESULT_OID:
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("diagnostics value \"%s\" is not allowed in current diagnostics statement",
! plpgsql_getdiag_kindname(ditem->kind)),
! parser_errposition(@1)));
! }
! }
! else
! {
! switch (ditem->kind)
! {
! /* these fields are disallowed in current diagnostics statement */
! case PLPGSQL_GETDIAG_ERROR_CONTEXT:
! case PLPGSQL_GETDIAG_ERROR_DETAIL:
! case PLPGSQL_GETDIAG_ERROR_HINT:
! case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
! case PLPGSQL_GETDIAG_MESSAGE_TEXT:
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("diagnostics value \"%s\" is not allowed in stacked diagnostics statement",
! plpgsql_getdiag_kindname(ditem->kind)),
! parser_errposition(@1)));
! }
! }
! }
$$ = (PLpgSQL_stmt *)new;
}
***************
*** 867,872 ****
--- 920,939 ----
}
;
+ getdiag_opt :
+ {
+ $$ = false;
+ }
+ | K_CURRENT
+ {
+ $$ = false;
+ }
+ | K_STACKED
+ {
+ $$ = true;
+ }
+ ;
+
getdiag_item :
{
int tok = yylex();
***************
*** 877,882 ****
--- 944,964 ----
else if (tok_is_keyword(tok, &yylval,
K_RESULT_OID, "result_oid"))
$$ = PLPGSQL_GETDIAG_RESULT_OID;
+ else if (tok_is_keyword(tok, &yylval,
+ K_EXCEPTION_DETAIL, "pg_exception_detail"))
+ $$ = PLPGSQL_GETDIAG_ERROR_DETAIL;
+ else if (tok_is_keyword(tok, &yylval,
+ K_EXCEPTION_HINT, "pg_exception_hint"))
+ $$ = PLPGSQL_GETDIAG_ERROR_HINT;
+ else if (tok_is_keyword(tok, &yylval,
+ K_EXCEPTION_CONTEXT, "pg_exception_context"))
+ $$ = PLPGSQL_GETDIAG_ERROR_CONTEXT;
+ else if (tok_is_keyword(tok, &yylval,
+ K_MESSAGE_TEXT, "message_text"))
+ $$ = PLPGSQL_GETDIAG_MESSAGE_TEXT;
+ else if (tok_is_keyword(tok, &yylval,
+ K_RETURNED_SQLSTATE, "returned_sqlstate"))
+ $$ = PLPGSQL_GETDIAG_RETURNED_SQLSTATE;
else
yyerror("unrecognized GET DIAGNOSTICS item");
}
***************
*** 2135,2146 ****
--- 2217,2232 ----
| K_ARRAY
| K_BACKWARD
| K_CONSTANT
+ | K_CURRENT
| K_CURSOR
| K_DEBUG
| K_DETAIL
| K_DUMP
| K_ERRCODE
| K_ERROR
+ | K_EXCEPTION_CONTEXT
+ | K_EXCEPTION_DETAIL
+ | K_EXCEPTION_HINT
| K_FIRST
| K_FORWARD
| K_HINT
***************
*** 2149,2154 ****
--- 2235,2241 ----
| K_LAST
| K_LOG
| K_MESSAGE
+ | K_MESSAGE_TEXT
| K_NEXT
| K_NO
| K_NOTICE
***************
*** 2157,2168 ****
--- 2244,2257 ----
| K_QUERY
| K_RELATIVE
| K_RESULT_OID
+ | K_RETURNED_SQLSTATE
| K_REVERSE
| K_ROW_COUNT
| K_ROWTYPE
| K_SCROLL
| K_SLICE
| K_SQLSTATE
+ | K_STACKED
| K_TYPE
| K_USE_COLUMN
| K_USE_VARIABLE
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2011-07-15 07:53:03.073787632 +0200
--- ./src/pl/plpgsql/src/pl_exec.c 2011-07-15 09:17:14.293046424 +0200
***************
*** 1411,1416 ****
--- 1411,1436 ----
return PLPGSQL_RC_OK;
}
+
+ /* ----------
+ * Small routine that ensure a transformation char *str to text datum.
+ * Use a empty string, when str pointer is NULL.
+ * ----------
+ */
+ static void
+ exec_assign_dg_text(PLpgSQL_execstate *estate, PLpgSQL_datum *var, const char *cstr)
+ {
+ bool isnull = false;
+ Datum value;
+
+ if (cstr != NULL)
+ value = PointerGetDatum(cstring_to_text(cstr));
+ else
+ value = PointerGetDatum(cstring_to_text(""));
+
+ exec_assign_value(estate, var, value, TEXTOID, &isnull);
+ }
+
/* ----------
* exec_stmt_getdiag Put internal PG information into
* specified variables.
***************
*** 1435,1440 ****
--- 1455,1469 ----
if (var == NULL)
continue;
+ /*
+ * Stacked diagnostics statement is based on processing of Error Data.
+ * These data should be available.
+ */
+ if (stmt->is_stacked && estate->cur_error == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_STACKED_DIAGNOSTICS_ACCESSED_WITHOUT_ACTIVE_HANDLER),
+ errmsg("stacked diagnostics accessed without active handler")));
+
switch (diag_item->kind)
{
case PLPGSQL_GETDIAG_ROW_COUNT:
***************
*** 1445,1456 ****
break;
case PLPGSQL_GETDIAG_RESULT_OID:
-
exec_assign_value(estate, var,
ObjectIdGetDatum(estate->eval_lastoid),
OIDOID, &isnull);
break;
default:
elog(ERROR, "unrecognized attribute request: %d",
diag_item->kind);
--- 1474,1509 ----
break;
case PLPGSQL_GETDIAG_RESULT_OID:
exec_assign_value(estate, var,
ObjectIdGetDatum(estate->eval_lastoid),
OIDOID, &isnull);
break;
+ case PLPGSQL_GETDIAG_ERROR_DETAIL:
+ exec_assign_dg_text(estate, var,
+ estate->cur_error->detail);
+ break;
+
+ case PLPGSQL_GETDIAG_ERROR_HINT:
+ exec_assign_dg_text(estate, var,
+ estate->cur_error->hint);
+ break;
+
+ case PLPGSQL_GETDIAG_ERROR_CONTEXT:
+ exec_assign_dg_text(estate, var,
+ estate->cur_error->context);
+ break;
+
+ case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
+ exec_assign_dg_text(estate, var,
+ unpack_sql_state(estate->cur_error->sqlerrcode));
+ break;
+
+ case PLPGSQL_GETDIAG_MESSAGE_TEXT:
+ exec_assign_dg_text(estate, var,
+ estate->cur_error->message);
+ break;
+
default:
elog(ERROR, "unrecognized attribute request: %d",
diag_item->kind);
*** ./src/pl/plpgsql/src/pl_funcs.c.orig 2011-07-15 07:53:03.074787623 +0200
--- ./src/pl/plpgsql/src/pl_funcs.c 2011-07-15 08:49:08.397831023 +0200
***************
*** 265,270 ****
--- 265,296 ----
return "unknown";
}
+ /*
+ * Returns a name of diagnostic value for GET DIAGNOSTICS statement
+ */
+ const char *
+ plpgsql_getdiag_kindname(int kind)
+ {
+ switch (kind)
+ {
+ case PLPGSQL_GETDIAG_ROW_COUNT:
+ return "ROW_COUNT";
+ case PLPGSQL_GETDIAG_RESULT_OID:
+ return "RESULT_OID";
+ case PLPGSQL_GETDIAG_ERROR_CONTEXT:
+ return "PG_EXCEPTION_CONTEXT";
+ case PLPGSQL_GETDIAG_ERROR_DETAIL:
+ return "PG_EXCEPTION_DETAIL";
+ case PLPGSQL_GETDIAG_ERROR_HINT:
+ return "PG_EXCEPTION_HINT";
+ case PLPGSQL_GETDIAG_RETURNED_SQLSTATE:
+ return "RETURNED_SQLSTATE";
+ case PLPGSQL_GETDIAG_MESSAGE_TEXT:
+ return "MESSAGE_TEXT";
+ }
+
+ return "unknown";
+ };
/**********************************************************************
* Release memory when a PL/pgSQL function is no longer needed
***************
*** 1389,1395 ****
ListCell *lc;
dump_ind();
! printf("GET DIAGNOSTICS ");
foreach(lc, stmt->diag_items)
{
PLpgSQL_diag_item *diag_item = (PLpgSQL_diag_item *) lfirst(lc);
--- 1415,1421 ----
ListCell *lc;
dump_ind();
! printf("GET %s DIAGNOSTICS ", stmt->is_stacked ? "STACKED" : "CURRENT");
foreach(lc, stmt->diag_items)
{
PLpgSQL_diag_item *diag_item = (PLpgSQL_diag_item *) lfirst(lc);
***************
*** 1397,1418 ****
if (lc != list_head(stmt->diag_items))
printf(", ");
! printf("{var %d} = ", diag_item->target);
!
! switch (diag_item->kind)
! {
! case PLPGSQL_GETDIAG_ROW_COUNT:
! printf("ROW_COUNT");
! break;
!
! case PLPGSQL_GETDIAG_RESULT_OID:
! printf("RESULT_OID");
! break;
!
! default:
! printf("???");
! break;
! }
}
printf("\n");
}
--- 1423,1430 ----
if (lc != list_head(stmt->diag_items))
printf(", ");
! printf("{var %d} = %s", diag_item->target,
! plpgsql_getdiag_kindname(diag_item->kind));
}
printf("\n");
}
*** ./src/pl/plpgsql/src/plpgsql.h.orig 2011-07-15 07:53:03.077787597 +0200
--- ./src/pl/plpgsql/src/plpgsql.h 2011-07-15 08:48:01.908423020 +0200
***************
*** 126,132 ****
enum
{
PLPGSQL_GETDIAG_ROW_COUNT,
! PLPGSQL_GETDIAG_RESULT_OID
};
/* --------
--- 126,137 ----
enum
{
PLPGSQL_GETDIAG_ROW_COUNT,
! PLPGSQL_GETDIAG_RESULT_OID,
! PLPGSQL_GETDIAG_ERROR_CONTEXT,
! PLPGSQL_GETDIAG_ERROR_DETAIL,
! PLPGSQL_GETDIAG_ERROR_HINT,
! PLPGSQL_GETDIAG_RETURNED_SQLSTATE,
! PLPGSQL_GETDIAG_MESSAGE_TEXT
};
/* --------
***************
*** 377,382 ****
--- 382,388 ----
int cmd_type;
int lineno;
List *diag_items; /* List of PLpgSQL_diag_item */
+ bool is_stacked; /* true when it access a second area of diagnostics info */
} PLpgSQL_stmt_getdiag;
***************
*** 929,934 ****
--- 935,941 ----
* ----------
*/
extern const char *plpgsql_stmt_typename(PLpgSQL_stmt *stmt);
+ extern const char *plpgsql_getdiag_kindname(int kind);
extern void plpgsql_free_function_memory(PLpgSQL_function *func);
extern void plpgsql_dumptree(PLpgSQL_function *func);
*** ./src/pl/plpgsql/src/pl_scanner.c.orig 2011-07-15 07:53:03.075787614 +0200
--- ./src/pl/plpgsql/src/pl_scanner.c 2011-07-15 08:01:04.531609103 +0200
***************
*** 110,115 ****
--- 110,116 ----
PG_KEYWORD("array", K_ARRAY, UNRESERVED_KEYWORD)
PG_KEYWORD("backward", K_BACKWARD, UNRESERVED_KEYWORD)
PG_KEYWORD("constant", K_CONSTANT, UNRESERVED_KEYWORD)
+ PG_KEYWORD("current", K_CURRENT, UNRESERVED_KEYWORD)
PG_KEYWORD("cursor", K_CURSOR, UNRESERVED_KEYWORD)
PG_KEYWORD("debug", K_DEBUG, UNRESERVED_KEYWORD)
PG_KEYWORD("detail", K_DETAIL, UNRESERVED_KEYWORD)
***************
*** 124,143 ****
--- 125,150 ----
PG_KEYWORD("last", K_LAST, UNRESERVED_KEYWORD)
PG_KEYWORD("log", K_LOG, UNRESERVED_KEYWORD)
PG_KEYWORD("message", K_MESSAGE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("message_text", K_MESSAGE_TEXT, UNRESERVED_KEYWORD)
PG_KEYWORD("next", K_NEXT, UNRESERVED_KEYWORD)
PG_KEYWORD("no", K_NO, UNRESERVED_KEYWORD)
PG_KEYWORD("notice", K_NOTICE, UNRESERVED_KEYWORD)
PG_KEYWORD("option", K_OPTION, UNRESERVED_KEYWORD)
+ PG_KEYWORD("pg_exception_context", K_EXCEPTION_CONTEXT, UNRESERVED_KEYWORD)
+ PG_KEYWORD("pg_exception_detail", K_EXCEPTION_DETAIL, UNRESERVED_KEYWORD)
+ PG_KEYWORD("pg_exception_hint", K_EXCEPTION_HINT, UNRESERVED_KEYWORD)
PG_KEYWORD("prior", K_PRIOR, UNRESERVED_KEYWORD)
PG_KEYWORD("query", K_QUERY, UNRESERVED_KEYWORD)
PG_KEYWORD("relative", K_RELATIVE, UNRESERVED_KEYWORD)
PG_KEYWORD("result_oid", K_RESULT_OID, UNRESERVED_KEYWORD)
+ PG_KEYWORD("returned_sqlstate", K_RETURNED_SQLSTATE, UNRESERVED_KEYWORD)
PG_KEYWORD("reverse", K_REVERSE, UNRESERVED_KEYWORD)
PG_KEYWORD("row_count", K_ROW_COUNT, UNRESERVED_KEYWORD)
PG_KEYWORD("rowtype", K_ROWTYPE, UNRESERVED_KEYWORD)
PG_KEYWORD("scroll", K_SCROLL, UNRESERVED_KEYWORD)
PG_KEYWORD("slice", K_SLICE, UNRESERVED_KEYWORD)
PG_KEYWORD("sqlstate", K_SQLSTATE, UNRESERVED_KEYWORD)
+ PG_KEYWORD("stacked", K_STACKED, UNRESERVED_KEYWORD)
PG_KEYWORD("type", K_TYPE, UNRESERVED_KEYWORD)
PG_KEYWORD("use_column", K_USE_COLUMN, UNRESERVED_KEYWORD)
PG_KEYWORD("use_variable", K_USE_VARIABLE, UNRESERVED_KEYWORD)
*** ./src/test/regress/expected/plpgsql.out.orig 2011-07-15 07:53:03.078787589 +0200
--- ./src/test/regress/expected/plpgsql.out 2011-07-15 08:01:04.534609076 +0200
***************
*** 4434,4436 ****
--- 4434,4511 ----
drop function foreach_test(anyarray);
drop type xy_tuple;
+ -- access to exception data
+ create or replace function bad_function()
+ returns int as $$
+ declare v int := 0;
+ begin
+ return 10 / v;
+ end;
+ $$ language plpgsql;
+ create or replace function raise_function()
+ returns void as $$
+ begin
+ raise exception 'custom exception'
+ using detail = 'some detail of custom exception',
+ hint = 'some hint related to custom exception';
+ end;
+ $$ language plpgsql;
+ create or replace function stacked_diagnostics_test01()
+ returns void as $$
+ declare _sqlstate text; _message text; _context text;
+ begin
+ perform bad_function();
+ exception when others then
+ get stacked diagnostics
+ _sqlstate = returned_sqlstate,
+ _message = message_text,
+ _context = pg_exception_context;
+ raise notice 'sqlstate: %, message: %, context: [%]', _sqlstate, _message, replace(_context, e'\n', ' <- ');
+ end;
+ $$ language plpgsql;
+ select stacked_diagnostics_test01();
+ NOTICE: sqlstate: 22012, message: division by zero, context: [PL/pgSQL function "bad_function" line 4 at RETURN <- SQL statement "SELECT bad_function()" <- PL/pgSQL function "stacked_diagnostics_test01" line 4 at PERFORM]
+ stacked_diagnostics_test01
+ ----------------------------
+
+ (1 row)
+
+ create or replace function stacked_diagnostics_test02()
+ returns void as $$
+ declare _detail text; _hint text; _message text;
+ begin
+ perform raise_function();
+ exception when others then
+ get stacked diagnostics
+ _message = message_text,
+ _detail = pg_exception_detail,
+ _hint = pg_exception_hint;
+ raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
+ end;
+ $$ language plpgsql;
+ select stacked_diagnostics_test02();
+ NOTICE: message: custom exception, detail: some detail of custom exception, hint: some hint related to custom exception
+ stacked_diagnostics_test02
+ ----------------------------
+
+ (1 row)
+
+ -- should to fail, cannot use stacked diagnostics statement outside handler
+ create or replace function stacked_diagnostics_test02()
+ returns void as $$
+ declare _detail text; _hint text; _message text;
+ begin
+ get stacked diagnostics
+ _message = message_text,
+ _detail = pg_exception_detail,
+ _hint = pg_exception_hint;
+ raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
+ end;
+ $$ language plpgsql;
+ select stacked_diagnostics_test02();
+ ERROR: stacked diagnostics accessed without active handler
+ CONTEXT: PL/pgSQL function "stacked_diagnostics_test02" line 4 at GET DIAGNOSTICS
+ drop function bad_function();
+ drop function raise_function();
+ drop function stacked_diagnostics_test01();
+ drop function stacked_diagnostics_test02();
*** ./src/test/regress/sql/plpgsql.sql.orig 2011-07-15 07:53:03.080787571 +0200
--- ./src/test/regress/sql/plpgsql.sql 2011-07-15 08:01:04.536609058 +0200
***************
*** 3489,3491 ****
--- 3489,3563 ----
drop function foreach_test(anyarray);
drop type xy_tuple;
+
+
+ -- access to exception data
+ create or replace function bad_function()
+ returns int as $$
+ declare v int := 0;
+ begin
+ return 10 / v;
+ end;
+ $$ language plpgsql;
+
+ create or replace function raise_function()
+ returns void as $$
+ begin
+ raise exception 'custom exception'
+ using detail = 'some detail of custom exception',
+ hint = 'some hint related to custom exception';
+ end;
+ $$ language plpgsql;
+
+ create or replace function stacked_diagnostics_test01()
+ returns void as $$
+ declare _sqlstate text; _message text; _context text;
+ begin
+ perform bad_function();
+ exception when others then
+ get stacked diagnostics
+ _sqlstate = returned_sqlstate,
+ _message = message_text,
+ _context = pg_exception_context;
+ raise notice 'sqlstate: %, message: %, context: [%]', _sqlstate, _message, replace(_context, e'\n', ' <- ');
+ end;
+ $$ language plpgsql;
+
+ select stacked_diagnostics_test01();
+
+ create or replace function stacked_diagnostics_test02()
+ returns void as $$
+ declare _detail text; _hint text; _message text;
+ begin
+ perform raise_function();
+ exception when others then
+ get stacked diagnostics
+ _message = message_text,
+ _detail = pg_exception_detail,
+ _hint = pg_exception_hint;
+ raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
+ end;
+ $$ language plpgsql;
+
+ select stacked_diagnostics_test02();
+
+ -- should to fail, cannot use stacked diagnostics statement outside handler
+
+ create or replace function stacked_diagnostics_test02()
+ returns void as $$
+ declare _detail text; _hint text; _message text;
+ begin
+ get stacked diagnostics
+ _message = message_text,
+ _detail = pg_exception_detail,
+ _hint = pg_exception_hint;
+ raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint;
+ end;
+ $$ language plpgsql;
+
+ select stacked_diagnostics_test02();
+
+ drop function bad_function();
+ drop function raise_function();
+ drop function stacked_diagnostics_test01();
+ drop function stacked_diagnostics_test02();
Re: patch: enhanced get diagnostics statement 2
Pavel Stehule <pavel.stehule@gmail.com> writes:
2011/7/14 Alvaro Herrera <alvherre@commandprompt.com>:
Thanks ... I expect you're going to resubmit the patch based on David's
last version and my comments?
yes, see a attachment
Applied with some editorial adjustments.
regards, tom lane