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

Reply via email to