Hello
2011/7/14 Alvaro Herrera <alvhe...@commandprompt.com>:
> Excerpts from Pavel Stehule's message of jue jul 14 16:25:56 -0400 2011:
>> 2011/7/14 Alvaro Herrera <alvhe...@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
> --
> Álvaro Herrera <alvhe...@commandprompt.com>
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
*** ./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();
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
