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
*** ./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();
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
