Hello

this patch adds possibility to set additional options (SQLSTATE,
DETAIL, DETAIL_LOG and HINT) for RAISE statement,

Proposal: http://archives.postgresql.org/pgsql-hackers/2008-04/msg00919.php

I changed keyword from WITH to USING, because I don't would to create
new keyword

RAISE level 'format' [, expression [, ...]] [ USING ( option =
expression [, ... ] ) ];

RAISE EXCEPTION 'Nonexistent ID --> %', user_id
  USING (hint = 'Please, check your user id');

Regards
Pavel Stehule
*** ./doc/src/sgml/plpgsql.sgml.orig	2008-04-16 11:17:51.000000000 +0200
--- ./doc/src/sgml/plpgsql.sgml	2008-04-16 12:59:44.000000000 +0200
***************
*** 2742,2748 ****
      raise errors.
  
  <synopsis>
! RAISE <replaceable class="parameter">level</replaceable> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ...</optional></optional>;
  </synopsis>
  
      Possible levels are <literal>DEBUG</literal>,
--- 2742,2748 ----
      raise errors.
  
  <synopsis>
! RAISE <replaceable class="parameter">level</replaceable> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ...</optional></optional> <optional> USING ( <replaceable class="parameter">option</replaceable> = <replaceable class="parameter">expression</replaceable> <optional>, ... </optional> ) </optional>;
  </synopsis>
  
      Possible levels are <literal>DEBUG</literal>,
***************
*** 2785,2801 ****
     <para>
      This example will abort the transaction with the given error message:
  <programlisting>
! RAISE EXCEPTION 'Nonexistent ID --> %', user_id;
  </programlisting>
     </para>
  
      <para>
!      <command>RAISE EXCEPTION</command> presently always generates
!      the same <varname>SQLSTATE</varname> code, <literal>P0001</>, no matter what message
       it is invoked with.  It is possible to trap this exception with
       <literal>EXCEPTION ... WHEN RAISE_EXCEPTION THEN ...</> but there
       is no way to tell one <command>RAISE</> from another.
      </para>
   </sect1>
  
   <sect1 id="plpgsql-trigger">
--- 2785,2808 ----
     <para>
      This example will abort the transaction with the given error message:
  <programlisting>
! RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING (hint = 'Please, check your user id');
  </programlisting>
     </para>
  
      <para>
!      <command>RAISE EXCEPTION</command> presently generates
!      the same <varname>SQLSTATE</varname> code, <literal>P0001</> , no matter what message
       it is invoked with.  It is possible to trap this exception with
       <literal>EXCEPTION ... WHEN RAISE_EXCEPTION THEN ...</> but there
       is no way to tell one <command>RAISE</> from another.
      </para>
+     
+     <para>
+       With additional options is possible set some log informaition related to 
+       raised exception. Possible options are <literal>SQLSTATE</literal>,
+       <literal>DETAIL</literal>, <literal>DETAIL_LOG</literal> and <literal>HINT</literal>.
+     </para> 
+     
   </sect1>
  
   <sect1 id="plpgsql-trigger">
*** ./src/pl/plpgsql/src/gram.y.orig	2008-04-15 07:37:03.000000000 +0200
--- ./src/pl/plpgsql/src/gram.y	2008-04-15 13:42:36.000000000 +0200
***************
*** 52,57 ****
--- 52,58 ----
  									  const char *end_label);
  static PLpgSQL_expr 	*read_cursor_args(PLpgSQL_var *cursor,
  										  int until, const char *expected);
+ static List *read_exception_additional_options();
  
  %}
  
***************
*** 1258,1263 ****
--- 1259,1265 ----
  						new->elog_level = $3;
  						new->message	= $4;
  						new->params		= NIL;
+ 						new->options		= NIL;
  
  						tok = yylex();
  
***************
*** 1266,1272 ****
  						 * indicates no parameters, or a comma that
  						 * begins the list of parameter expressions
  						 */
! 						if (tok != ',' && tok != ';')
  							yyerror("syntax error");
  
  						if (tok == ',')
--- 1268,1274 ----
  						 * indicates no parameters, or a comma that
  						 * begins the list of parameter expressions
  						 */
! 						if (tok != ',' && tok != ';' && tok != K_USING)
  							yyerror("syntax error");
  
  						if (tok == ',')
***************
*** 1274,1286 ****
  							do
  							{
  								PLpgSQL_expr *expr;
! 
! 								expr = read_sql_expression2(',', ';',
! 															", or ;",
! 															&tok);
  								new->params = lappend(new->params, expr);
  							} while (tok == ',');
  						}
  
  						$$ = (PLpgSQL_stmt *)new;
  					}
--- 1276,1294 ----
  							do
  							{
  								PLpgSQL_expr *expr;
! 								
! 								expr = read_sql_construct(',', ';', K_USING, ", or ; or USING",
! 												    "SELECT ", true, true, &tok);
  								new->params = lappend(new->params, expr);
  							} while (tok == ',');
  						}
+ 						
+ 						if (tok == K_USING)
+ 						{
+ 							new->options = read_exception_additional_options();
+ 							if (yylex() != ';')
+ 								yyerror("syntax error");
+ 						}
  
  						$$ = (PLpgSQL_stmt *)new;
  					}
***************
*** 2633,2638 ****
--- 2641,2704 ----
  }
  
  
+ /*
+  * Procedure read RAISE statements additional options
+  */
+ static List *
+ read_exception_additional_options()
+ {
+ 	List *result = NIL;
+ 	
+ 	if (yylex() != '(')
+ 		yyerror("syntax error, expected \"(\"");
+ 	
+ 	for(;;)
+ 	{
+ 		int	tok;
+ 		PLpgSQL_raise_option *opt;
+ 
+ 		if ((tok = yylex()) == 0)
+ 			yyerror("unexpected end of function definition");
+ 
+ 		opt = (PLpgSQL_raise_option *) palloc(sizeof(PLpgSQL_raise_option));
+ 		if (pg_strcasecmp(yytext, "sqlstate") == 0)
+ 		{
+ 			opt->opt_type = PLPGSQL_RAISEOPTION_SQLSTATE;
+ 		}
+ 		else if (pg_strcasecmp(yytext, "detail") == 0)
+ 		{
+ 			opt->opt_type = PLPGSQL_RAISEOPTION_DETAIL;
+     		}
+ 		else if (pg_strcasecmp(yytext, "detail_log") == 0)
+ 		{
+ 			opt->opt_type = PLPGSQL_RAISEOPTION_DETAIL_LOG;
+ 		}
+ 		else if (pg_strcasecmp(yytext, "hint") == 0)
+ 		{
+ 			opt->opt_type = PLPGSQL_RAISEOPTION_HINT;
+ 		}
+ 		else
+ 		{
+ 			plpgsql_error_lineno = plpgsql_scanner_lineno();
+ 			ereport(ERROR,
+ 					(errcode(ERRCODE_SYNTAX_ERROR),
+ 					 errmsg("unknown RAISE statement option \"%s\"",
+ 							yytext)));
+ 		}
+ 			
+ 		if (yylex() != K_ASSIGN)
+ 			yyerror("syntax error, expected \"=\"");			
+ 
+ 		opt->expr = read_sql_expression2(',', ')', ", or )", &tok);
+ 		result = lappend(result, opt);
+ 		
+ 		if (tok == ')')
+ 			break;	
+ 	}
+ 		
+ 	return result;	
+ }	
+ 
  /* Needed to avoid conflict between different prefix settings: */
  #undef yylex
  
*** ./src/pl/plpgsql/src/pl_exec.c.orig	2008-04-15 11:40:42.000000000 +0200
--- ./src/pl/plpgsql/src/pl_exec.c	2008-04-15 13:37:38.000000000 +0200
***************
*** 2318,2327 ****
  	 */
  	estate->err_text = raise_skip_msg;	/* suppress traceback of raise */
  
! 	ereport(stmt->elog_level,
! 		 ((stmt->elog_level >= ERROR) ? errcode(ERRCODE_RAISE_EXCEPTION) : 0,
! 		  errmsg_internal("%s", plpgsql_dstring_get(&ds))));
  
  	estate->err_text = NULL;	/* un-suppress... */
  
  	plpgsql_dstring_free(&ds);
--- 2318,2385 ----
  	 */
  	estate->err_text = raise_skip_msg;	/* suppress traceback of raise */
  
! 	if (stmt->options != NIL)
! 	{
! 		/* evaluate RAISE options */
! 		ListCell	*l;
! 		
! 		errstart(stmt->elog_level, __FILE__, __LINE__, PG_FUNCNAME_MACRO);
! 
! 		errmsg_internal("%s", plpgsql_dstring_get(&ds));
! 		
! 		foreach(l, stmt->options)
! 		{
! 			Oid			optiontypeid;
! 			Datum		optionvalue;
! 			bool		optionisnull;
! 			char	   *extval;
! 
! 			PLpgSQL_raise_option *opt = (PLpgSQL_raise_option *) lfirst(l);
  
+ 			optionvalue = exec_eval_expr(estate, opt->expr,
+ 										&optionisnull,
+ 										&optiontypeid);
+ 
+ 			if (optionisnull)
+ 				ereport(ERROR,
+ 					    (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ 					     errmsg("RAISE statement option cannot ne NULL")));
+ 			
+ 			extval = convert_value_to_string(optionvalue, optiontypeid);
+ 			
+ 			//exec_eval_cleanup(estate);
+ 			
+ 			switch (opt->opt_type)
+ 			{
+ 				case PLPGSQL_RAISEOPTION_DETAIL_LOG:
+ 					errdetail_log(extval);
+ 					break;
+ 				case PLPGSQL_RAISEOPTION_DETAIL:
+ 					errdetail(extval);
+ 					break;
+ 				case PLPGSQL_RAISEOPTION_HINT:
+ 					errhint(extval);
+ 					break;
+ 				case PLPGSQL_RAISEOPTION_SQLSTATE:
+ 					if (strlen(extval) != 5)
+ 						ereport(ERROR,
+ 							    (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ 							     errmsg("sqlstate has five characters")));
+ 					errcode(MAKE_SQLSTATE(extval[0], extval[1], extval[2],
+ 					                      extval[3], extval[4]));
+ 					break;
+ 			}
+ 		}
+ 		
+ 		errfinish(0);
+ 		
+ 	}
+ 	else
+ 	{
+ 		ereport(stmt->elog_level,
+ 			 ((stmt->elog_level >= ERROR) ? errcode(ERRCODE_RAISE_EXCEPTION) : 0,
+ 			errmsg_internal("%s", plpgsql_dstring_get(&ds))));
+ 	}
  	estate->err_text = NULL;	/* un-suppress... */
  
  	plpgsql_dstring_free(&ds);
*** ./src/pl/plpgsql/src/plpgsql.h.orig	2008-04-15 08:23:46.000000000 +0200
--- ./src/pl/plpgsql/src/plpgsql.h	2008-04-15 12:42:02.000000000 +0200
***************
*** 120,125 ****
--- 120,137 ----
  };
  
  
+ /* --------
+  * RAISE statement options
+  * --------
+  */
+ enum
+ {
+ 	PLPGSQL_RAISEOPTION_SQLSTATE,
+ 	PLPGSQL_RAISEOPTION_DETAIL,
+ 	PLPGSQL_RAISEOPTION_DETAIL_LOG,
+ 	PLPGSQL_RAISEOPTION_HINT
+ }; 
+ 
  /**********************************************************************
   * Node and structure definitions
   **********************************************************************/
***************
*** 539,546 ****
--- 551,564 ----
  	int			elog_level;
  	char	   *message;
  	List	   *params;			/* list of expressions */
+ 	List	   *options;			/* elog options */
  } PLpgSQL_stmt_raise;
  
+ typedef struct
+ {								/* RAISE statement's option */
+ 	int 		opt_type;
+ 	PLpgSQL_expr *expr;
+ } PLpgSQL_raise_option;
  
  typedef struct
  {								/* Generic SQL statement to execute */
*** ./src/test/regress/expected/plpgsql.out.orig	2008-04-16 13:23:12.000000000 +0200
--- ./src/test/regress/expected/plpgsql.out	2008-04-16 13:19:27.000000000 +0200
***************
*** 3267,3269 ****
--- 3267,3281 ----
  $$ language plpgsql;
  ERROR:  cursor FOR loop must use a bound cursor variable
  CONTEXT:  compile of PL/pgSQL function "forc_bad" near line 4
+ -- specified sqlstate is invisible, we can't set VERBOSITY verbose
+ -- because LOCATION field contains row number from pl_exec.c
+ create or replace function raise_options() returns void as $$
+ begin
+   raise exception '% % %', 1, 2, 3 using (sqlstate = '55001', detail = 'some detail info', hint = 'some hint');
+ end;
+ $$ language plpgsql;
+ select raise_options();
+ ERROR:  1 2 3
+ DETAIL:  some detail info
+ HINT:  some hint
+ drop function raise_options();
*** ./src/test/regress/sql/plpgsql.sql.orig	2008-04-16 13:03:14.000000000 +0200
--- ./src/test/regress/sql/plpgsql.sql	2008-04-16 13:18:53.000000000 +0200
***************
*** 2669,2671 ****
--- 2669,2683 ----
    end loop;
  end;
  $$ language plpgsql;
+ 
+ -- specified sqlstate is invisible, we can't set VERBOSITY verbose
+ -- because LOCATION field contains row number from pl_exec.c
+ create or replace function raise_options() returns void as $$
+ begin
+   raise exception '% % %', 1, 2, 3 using (sqlstate = '55001', detail = 'some detail info', hint = 'some hint');
+ end;
+ $$ language plpgsql;
+ 
+ select raise_options();
+ drop function raise_options();
+ 
-- 
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Reply via email to