Pavel Stehule wrote:
Per small recent discussion I corrected patch user's exception.
Attached is a revised patch. I haven't looked at the documentation
changes yet (more work is needed I believe) or some of the error message
text.
I was originally hoping to make "exception variables" a little more
full-featured -- it seems silly to DECLARE something that cannot be
initialized with the value of another expression, for example. I can
also see how it would be useful to evaluate an expression variable (e.g.
to print it out for debugging purposes). It would be possible extend the
operations allowed upon exception variables, thinking about this
further, I wonder if there is any point introducing the concept of an
"exception variable" in the first place. What does it buy us over simply
using a string? In other words, if we allowed the syntax:
RAISE LEVEL [ opt_sqlstate ] 'fmt' [, expr ... ]
where `opt_sqlstate' is either empty, a T_WORD we find in the table of
predefined condition names, or an expression that evaluates to a text
value. The text value must be of a certain form (e.g. 5 characters in
length, begins with a "U" and so on).
It might be slightly more difficult to parse this (especially if we
allow 'fmt' to be an expression yielding a string, not just a string
literal), but I don't think it is ambiguous and can be sorted out via
yylex().
-Neil
Index: doc/src/sgml/plpgsql.sgml
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/doc/src/sgml/plpgsql.sgml,v
retrieving revision 1.75
diff -c -r1.75 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml 2 Jul 2005 08:59:47 -0000 1.75
--- doc/src/sgml/plpgsql.sgml 6 Jul 2005 13:26:22 -0000
***************
*** 2117,2123 ****
<para>
The <replaceable>condition</replaceable> names can be any of those
shown in <xref linkend="errcodes-appendix">. A category name matches
! any error within its category.
The special condition name <literal>OTHERS</>
matches every error type except <literal>QUERY_CANCELED</>.
(It is possible, but often unwise, to trap
--- 2117,2125 ----
<para>
The <replaceable>condition</replaceable> names can be any of those
shown in <xref linkend="errcodes-appendix">. A category name matches
! any error within its category. You can use exception variable as
! condition name. Exception variable is declared with type
! <literal>EXCEPTION</literal>
The special condition name <literal>OTHERS</>
matches every error type except <literal>QUERY_CANCELED</>.
(It is possible, but often unwise, to trap
***************
*** 2571,2577 ****
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>,
--- 2573,2580 ----
raise errors.
<synopsis>
! RAISE <replaceable class="parameter">level</replaceable>
! <optional>system exception|exception variable</optional> '<replaceable
class="parameter">format</replaceable>' <optional>, <replaceable
class="parameter">expression</replaceable> <optional>,
...</optional></optional>;
</synopsis>
Possible levels are <literal>DEBUG</literal>,
***************
*** 2588,2593 ****
--- 2591,2600 ----
variables. See <xref linkend="runtime-config"> for more
information.
</para>
+
+ <para>
+ You can specify any system exception or any user exception.
+ </para>
<para>
Inside the format string, <literal>%</literal> is replaced by the
Index: src/include/utils/elog.h
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/include/utils/elog.h,v
retrieving revision 1.79
diff -c -r1.79 elog.h
*** src/include/utils/elog.h 10 Jun 2005 16:23:10 -0000 1.79
--- src/include/utils/elog.h 6 Jul 2005 13:26:22 -0000
***************
*** 61,66 ****
--- 61,72 ----
(PGSIXBIT(ch1) + (PGSIXBIT(ch2) << 6) + (PGSIXBIT(ch3) << 12) + \
(PGSIXBIT(ch4) << 18) + (PGSIXBIT(ch5) << 24))
+ #define MAKE_SQLSTATE_STR(str) \
+ ( \
+ AssertMacro(strlen(str) == 5), \
+ MAKE_SQLSTATE(str[0], str[1], str[2], str[3], str[4]) \
+ )
+
/* These macros depend on the fact that '0' becomes a zero in SIXBIT */
#define ERRCODE_TO_CATEGORY(ec) ((ec) & ((1 << 12) - 1))
#define ERRCODE_IS_CATEGORY(ec) (((ec) & ~((1 << 12) - 1)) == 0)
Index: src/pl/plpgsql/src/gram.y
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.80
diff -c -r1.80 gram.y
*** src/pl/plpgsql/src/gram.y 2 Jul 2005 17:01:59 -0000 1.80
--- src/pl/plpgsql/src/gram.y 6 Jul 2005 13:38:35 -0000
***************
*** 103,108 ****
--- 103,109 ----
PLpgSQL_exception_block *exception_block;
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
+ PLpgSQL_user_exc *user_exc;
}
%type <declhdr> decl_sect
***************
*** 142,150 ****
%type <exception_block> exception_sect
%type <exception> proc_exception
%type <condition> proc_conditions
!
! %type <ival> raise_level
%type <str> raise_msg
%type <list> getdiag_list
--- 143,152 ----
%type <exception_block> exception_sect
%type <exception> proc_exception
%type <condition> proc_conditions
+ %type <str> exception_name
+ %type <ival> decl_sqlstate sqlstate_defn
! %type <ival> raise_level opt_raise_exc
%type <str> raise_msg
%type <list> getdiag_list
***************
*** 221,226 ****
--- 223,229 ----
%token T_LABEL
%token T_WORD
%token T_ERROR
+ %token T_EXCEPTION
%token O_OPTION
%token O_DUMP
***************
*** 348,354 ****
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("row or record variable cannot be NOT NULL")));
}
! if ($5 != NULL)
{
if (var->dtype ==
PLPGSQL_DTYPE_VAR)
((PLpgSQL_var
*) var)->default_val = $5;
--- 351,357 ----
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("row or record variable cannot be NOT NULL")));
}
! if ($5)
{
if (var->dtype ==
PLPGSQL_DTYPE_VAR)
((PLpgSQL_var
*) var)->default_val = $5;
***************
*** 358,363 ****
--- 361,378 ----
errmsg("default value for row or record variable is not supported")));
}
}
+ | decl_varname K_EXCEPTION decl_sqlstate
+ {
+ PLpgSQL_user_exc *exc_var;
+ PLpgSQL_type dtype;
+
+ dtype.typname = "exception";
+ dtype.ttype =
PLPGSQL_TTYPE_EXCEPTION;
+
+ exc_var = (PLpgSQL_user_exc *)
plpgsql_build_variable($1.name, $1.lineno,
+
&dtype, true);
+ exc_var->sqlstate = $3;
+ }
| decl_varname K_ALIAS K_FOR decl_aliasitem ';'
{
plpgsql_ns_additem($4->itemtype,
***************
*** 563,572 ****
| K_DEFAULT
;
! proc_sect :
{
! $$ = NIL;
}
| proc_stmts
{ $$ = $1; }
;
--- 578,615 ----
| K_DEFAULT
;
! decl_sqlstate : ';'
! { $$ = plpgsql_new_user_sqlstate(); }
! | decl_defkey sqlstate_defn ';'
{
! $$ = $2;
}
+ ;
+
+ sqlstate_defn : T_STRING
+ {
+ char *state_str =
plpgsql_get_string_value();
+
+ if (strlen(state_str) != 5)
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+
errmsg("invalid SQLSTATE value \"%s\" for "
+
"user-defined exception", state_str),
+
errdetail("SQLSTATE values must be 5 characters in length")));
+
+ if (strncmp(state_str, "U0", 2)
== 0)
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+
errmsg("invalid SQLSTATE value \"%s\" for "
+
"user-defined exception", state_str),
+
errhint("Class \"U0\" is reserved for default values user's exceptions.")));
+
+ $$ =
MAKE_SQLSTATE_STR(state_str);
+ }
+ ;
+
+ proc_sect :
+ { $$ = NIL; }
| proc_stmts
{ $$ = $1; }
;
***************
*** 1184,1190 ****
}
;
! stmt_raise : K_RAISE lno raise_level raise_msg
{
PLpgSQL_stmt_raise
*new;
int tok;
--- 1227,1233 ----
}
;
! stmt_raise : K_RAISE lno raise_level opt_raise_exc raise_msg
{
PLpgSQL_stmt_raise
*new;
int tok;
***************
*** 1194,1202 ****
new->cmd_type =
PLPGSQL_STMT_RAISE;
new->lineno = $2;
new->elog_level = $3;
! new->message = $4;
new->params = NIL;
tok = yylex();
/*
--- 1237,1256 ----
new->cmd_type =
PLPGSQL_STMT_RAISE;
new->lineno = $2;
new->elog_level = $3;
! new->message = $5;
new->params = NIL;
+ /* No exception variable or
SQLSTATE value specified? */
+ if ($4 == -1)
+ {
+ if (new->elog_level >=
ERROR)
+ new->sqlstate =
ERRCODE_RAISE_EXCEPTION;
+ else
+ new->sqlstate =
0;
+ }
+ else
+ new->sqlstate = $4;
+
tok = yylex();
/*
***************
*** 1266,1271 ****
--- 1320,1344 ----
}
;
+ opt_raise_exc : T_EXCEPTION
+ {
+ $$ = yylval.user_exc->sqlstate;
+ }
+ | T_WORD
+ {
+ PLpgSQL_condition *c =
plpgsql_parse_err_condition(yytext);
+ /* Don't allow "OTHERS" to be
thrown via RAISE */
+ if (c->sqlerrstate == 0)
+ yyerror("illegal
SQLSTATE value");
+ $$ = c->sqlerrstate;
+ pfree(c);
+ }
+ | /* EMPTY */
+ {
+ $$ = -1;
+ }
+ ;
+
stmt_execsql : execsql_start lno
{
PLpgSQL_stmt_execsql *new;
***************
*** 1285,1292 ****
PLpgSQL_expr *expr;
int endtoken;
! expr =
read_sql_construct(K_INTO, ';', "INTO|;", "SELECT ",
!
true, true, &endtoken);
new =
palloc(sizeof(PLpgSQL_stmt_dynexecute));
new->cmd_type =
PLPGSQL_STMT_DYNEXECUTE;
--- 1358,1366 ----
PLpgSQL_expr *expr;
int endtoken;
! expr =
read_sql_construct(K_INTO, ';', "INTO or ;",
!
"SELECT ", true, true,
!
&endtoken);
new =
palloc(sizeof(PLpgSQL_stmt_dynexecute));
new->cmd_type =
PLPGSQL_STMT_DYNEXECUTE;
***************
*** 1581,1600 ****
}
;
! proc_conditions : proc_conditions K_OR opt_lblname
! {
! PLpgSQL_condition
*old;
! for (old = $1;
old->next != NULL; old = old->next)
! /* skip */ ;
! old->next =
plpgsql_parse_err_condition($3);
! $$ = $1;
! }
! | opt_lblname
! {
! $$ =
plpgsql_parse_err_condition($1);
! }
;
expr_until_semi :
--- 1655,1686 ----
}
;
! proc_conditions : proc_conditions K_OR exception_name
! {
! PLpgSQL_condition *old;
!
! for (old = $1; old->next !=
NULL; old = old->next)
! /* skip */ ;
! old->next =
plpgsql_parse_err_condition($3);
! $$ = $1;
! }
! | exception_name
! {
! $$ =
plpgsql_parse_err_condition($1);
! }
! ;
! exception_name : T_WORD
! {
! char *name;
! plpgsql_convert_ident(yytext,
&name, 1);
! $$ = name;
! }
! | T_EXCEPTION
! {
! $$ = yylval.user_exc->refname;
! }
;
expr_until_semi :
Index: src/pl/plpgsql/src/pl_comp.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/pl/plpgsql/src/pl_comp.c,v
retrieving revision 1.91
diff -c -r1.91 pl_comp.c
*** src/pl/plpgsql/src/pl_comp.c 10 Jun 2005 16:23:11 -0000 1.91
--- src/pl/plpgsql/src/pl_comp.c 6 Jul 2005 13:26:22 -0000
***************
*** 80,85 ****
--- 80,87 ----
bool plpgsql_DumpExecTree = false;
bool plpgsql_check_syntax = false;
+ static int plpgsql_user_exc_counter;
+
PLpgSQL_function *plpgsql_curr_compile;
/* A context appropriate for short-term allocs during compilation */
***************
*** 316,321 ****
--- 318,325 ----
plpgsql_Datums = palloc(sizeof(PLpgSQL_datum *) * datums_alloc);
datums_last = 0;
+ plpgsql_user_exc_counter = 0;
+
/*
* Do extra syntax checks when validating the function
* definition. We skip this when actually compiling functions for
***************
*** 905,910 ****
--- 909,918 ----
plpgsql_yylval.row = (PLpgSQL_row *)
(plpgsql_Datums[nse->itemno]);
return T_ROW;
+ case PLPGSQL_NSTYPE_EXCEPTION:
+ plpgsql_yylval.user_exc = (PLpgSQL_user_exc *)
(plpgsql_Datums[nse->itemno]);
+ return T_EXCEPTION;
+
default:
return T_ERROR;
}
***************
*** 1626,1631 ****
--- 1634,1658 ----
result = (PLpgSQL_variable *) rec;
break;
}
+ case PLPGSQL_TTYPE_EXCEPTION:
+ {
+ /* The exception type */
+ PLpgSQL_user_exc *exception;
+
+ exception = palloc0(sizeof(PLpgSQL_user_exc));
+ exception->dtype = PLPGSQL_DTYPE_EXCEPTION;
+ exception->refname = pstrdup(refname);
+ exception->lineno = lineno;
+ /* caller should define sqlstate! */
+
+ plpgsql_adddatum((PLpgSQL_datum *) exception);
+ if (add2namespace)
+
plpgsql_ns_additem(PLPGSQL_NSTYPE_EXCEPTION,
+
exception->dno,
+
refname);
+ result = (PLpgSQL_variable *) exception;
+ break;
+ }
case PLPGSQL_TTYPE_PSEUDO:
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
***************
*** 1883,1890 ****
* plpgsql_parse_err_condition
* Generate PLpgSQL_condition entry(s) for an exception condition
name
*
! * This has to be able to return a list because there are some duplicate
! * names in the table of error code names.
*/
PLpgSQL_condition *
plpgsql_parse_err_condition(char *condname)
--- 1910,1919 ----
* plpgsql_parse_err_condition
* Generate PLpgSQL_condition entry(s) for an exception condition
name
*
! * This has to be able to return a list because there are some
! * duplicate names in the table of error code names. Note that the
! * exception name should already be normalized (e.g. via
! * plpgsql_convert_ident).
*/
PLpgSQL_condition *
plpgsql_parse_err_condition(char *condname)
***************
*** 1892,1902 ****
int i;
PLpgSQL_condition *new;
PLpgSQL_condition *prev;
!
! /*
! * XXX Eventually we will want to look for user-defined exception
! * names here.
! */
/*
* OTHERS is represented as code 0 (which would map to '00000', but we
--- 1921,1927 ----
int i;
PLpgSQL_condition *new;
PLpgSQL_condition *prev;
! PLpgSQL_nsitem *nse;
/*
* OTHERS is represented as code 0 (which would map to '00000', but we
***************
*** 1924,1929 ****
--- 1949,1981 ----
}
}
+ /*
+ * If "condname" is not the name of any builtin exceptions, look
+ * for a user-defined exception variable with that name.
+ */
+ if (!prev)
+ {
+ /*
+ * Do a lookup on the compiler's namestack
+ */
+ nse = plpgsql_ns_lookup(condname, NULL);
+
+ if (nse != NULL)
+ {
+ PLpgSQL_user_exc *exc_var;
+
+ exc_var = (PLpgSQL_user_exc *)
(plpgsql_Datums[nse->itemno]);
+ if (nse->itemtype == PLPGSQL_NSTYPE_EXCEPTION)
+ {
+ new = palloc(sizeof(PLpgSQL_condition));
+ new->sqlerrstate = exc_var->sqlstate;
+ new->condname = condname;
+ new->next = prev;
+ prev = new;
+ }
+ }
+ }
+
if (!prev)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
***************
*** 2171,2179 ****
plpgsql_HashEnt *hentry;
hentry = (plpgsql_HashEnt *) hash_search(plpgsql_HashTable,
!
(void *) function->fn_hashkey,
HASH_REMOVE,
NULL);
if (hentry == NULL)
elog(WARNING, "trying to delete function that does not exist");
}
--- 2223,2246 ----
plpgsql_HashEnt *hentry;
hentry = (plpgsql_HashEnt *) hash_search(plpgsql_HashTable,
!
(void *) function->fn_hashkey,
HASH_REMOVE,
NULL);
if (hentry == NULL)
elog(WARNING, "trying to delete function that does not exist");
}
+
+ #define MAX_USER_EXCPT 999
+
+ int
+ plpgsql_new_user_sqlstate(void)
+ {
+ char str[6];
+
+ if (plpgsql_user_exc_counter >= MAX_USER_EXCPT)
+ ereport(ERROR,
+ (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+ errmsg("Too many user-defined exceptions")));
+ snprintf(str, sizeof(str), "U0%03d", ++plpgsql_user_exc_counter);
+ return MAKE_SQLSTATE_STR(str);
+ }
Index: src/pl/plpgsql/src/pl_exec.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.149
diff -c -r1.149 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c 26 Jun 2005 22:05:42 -0000 1.149
--- src/pl/plpgsql/src/pl_exec.c 6 Jul 2005 13:56:49 -0000
***************
*** 723,728 ****
--- 723,733 ----
result = datum;
break;
+ case PLPGSQL_DTYPE_EXCEPTION:
+ /* XXX: this is read-only at runtime -- just copy as
well? */
+ result = NULL;
+ break;
+
default:
elog(ERROR, "unrecognized dtype: %d", datum->dtype);
result = NULL; /* keep compiler quiet */
***************
*** 825,830 ****
--- 830,836 ----
case PLPGSQL_DTYPE_RECFIELD:
case PLPGSQL_DTYPE_ARRAYELEM:
+ case PLPGSQL_DTYPE_EXCEPTION:
break;
default:
***************
*** 2062,2069 ****
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... */
--- 2068,2075 ----
estate->err_text = raise_skip_msg; /* suppress traceback of raise
*/
ereport(stmt->elog_level,
! (errcode(stmt->sqlstate),
! errmsg_internal("%s", plpgsql_dstring_get(&ds))));
estate->err_text = NULL; /* un-suppress... */
Index: src/pl/plpgsql/src/plpgsql.h
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/pl/plpgsql/src/plpgsql.h,v
retrieving revision 1.64
diff -c -r1.64 plpgsql.h
*** src/pl/plpgsql/src/plpgsql.h 22 Jun 2005 01:35:02 -0000 1.64
--- src/pl/plpgsql/src/plpgsql.h 6 Jul 2005 13:26:22 -0000
***************
*** 58,64 ****
PLPGSQL_NSTYPE_LABEL,
PLPGSQL_NSTYPE_VAR,
PLPGSQL_NSTYPE_ROW,
! PLPGSQL_NSTYPE_REC
};
/* ----------
--- 58,65 ----
PLPGSQL_NSTYPE_LABEL,
PLPGSQL_NSTYPE_VAR,
PLPGSQL_NSTYPE_ROW,
! PLPGSQL_NSTYPE_REC,
! PLPGSQL_NSTYPE_EXCEPTION
};
/* ----------
***************
*** 73,79 ****
PLPGSQL_DTYPE_RECFIELD,
PLPGSQL_DTYPE_ARRAYELEM,
PLPGSQL_DTYPE_EXPR,
! PLPGSQL_DTYPE_TRIGARG
};
/* ----------
--- 74,81 ----
PLPGSQL_DTYPE_RECFIELD,
PLPGSQL_DTYPE_ARRAYELEM,
PLPGSQL_DTYPE_EXPR,
! PLPGSQL_DTYPE_TRIGARG,
! PLPGSQL_DTYPE_EXCEPTION
};
/* ----------
***************
*** 85,91 ****
PLPGSQL_TTYPE_SCALAR, /* scalar types and domains */
PLPGSQL_TTYPE_ROW, /* composite types */
PLPGSQL_TTYPE_REC, /* RECORD pseudotype */
! PLPGSQL_TTYPE_PSEUDO /* other pseudotypes */
};
/* ----------
--- 87,94 ----
PLPGSQL_TTYPE_SCALAR, /* scalar types and domains */
PLPGSQL_TTYPE_ROW, /* composite types */
PLPGSQL_TTYPE_REC, /* RECORD pseudotype */
! PLPGSQL_TTYPE_PSEUDO, /* other pseudotypes */
! PLPGSQL_TTYPE_EXCEPTION /* user-defined exception variables */
};
/* ----------
***************
*** 173,179 ****
* PLpgSQL_trigarg
*/
typedef struct
! { /* Generic
datum array item */
int dtype;
int dno;
} PLpgSQL_datum;
--- 176,182 ----
* PLpgSQL_trigarg
*/
typedef struct
! { /* Generic
datum array item */
int dtype;
int dno;
} PLpgSQL_datum;
***************
*** 190,197 ****
int lineno;
} PLpgSQL_variable;
typedef struct PLpgSQL_expr
! { /* SQL Query to
plan and execute */
int dtype;
int exprno;
char *query;
--- 193,209 ----
int lineno;
} PLpgSQL_variable;
+ typedef struct
+ { /* User-defined
exception variable */
+ int dtype;
+ int dno;
+ char *refname;
+ int lineno;
+ int sqlstate;
+ } PLpgSQL_user_exc;
+
typedef struct PLpgSQL_expr
! { /* SQL Query to
plan and execute */
int dtype;
int exprno;
char *query;
***************
*** 292,298 ****
typedef struct
! { /* Item in the
compilers namestack */
int itemtype;
int itemno;
char name[1];
--- 304,310 ----
typedef struct
! { /* Item in the
compiler's namestack */
int itemtype;
int itemno;
char name[1];
***************
*** 516,521 ****
--- 528,534 ----
int cmd_type;
int lineno;
int elog_level;
+ int sqlstate;
char *message;
List *params; /* list of expressions */
} PLpgSQL_stmt_raise;
***************
*** 688,693 ****
--- 701,708 ----
extern int plpgsql_add_initdatums(int **varnos);
extern void plpgsql_HashTableInit(void);
extern void plpgsql_compile_error_callback(void *arg);
+ extern int plpgsql_new_user_sqlstate(void);
+
/* ----------
* Functions in pl_handler.c
Index: src/test/regress/expected/plpgsql.out
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/test/regress/expected/plpgsql.out,v
retrieving revision 1.38
diff -c -r1.38 plpgsql.out
*** src/test/regress/expected/plpgsql.out 2 Jul 2005 08:59:48 -0000
1.38
--- src/test/regress/expected/plpgsql.out 6 Jul 2005 15:03:56 -0000
***************
*** 2721,2723 ****
--- 2721,2778 ----
$$ language plpgsql;
ERROR: end label "outer_label" specified for unlabelled block
CONTEXT: compile of PL/pgSQL function "end_label4" near line 5
+ -- user-defined exceptions
+ -- should fail: illegal sqlstate for exception
+ create function innerfx() returns void as $$
+ declare
+ my_exc exception = 'U0001';
+ begin -- using msgtext as one param of exception
+ raise exception my_exc '%', CURRENT_TIMESTAMP;
+ end;
+ $$ language plpgsql;
+ ERROR: invalid SQLSTATE value "U0001" for user-defined exception
+ HINT: Class "U0" is reserved for default values user's exceptions.
+ CONTEXT: compile of PL/pgSQL function "innerfx" near line 2
+ create function innerfx() returns integer as $$
+ declare
+ my_exc1 exception = 'U1001';
+ begin -- using msgtext as one param of exception
+ raise exception my_exc1 '%', 100.34::numeric;
+ return 1;
+ end $$ language plpgsql;
+ create function outerfx() returns integer as $$
+ declare
+ my_excpt exception = 'U1001';
+ alias_div_by_zero exception = '22012';
+ def_sqlstate exception;
+ begin
+ begin
+ raise exception def_sqlstate 'foo';
+ exception when def_sqlstate then
+ raise notice '01 catch: %, %', sqlstate, sqlerrm;
+ end;
+ begin
+ raise notice '%', innerfx();
+ exception when my_excpt then
+ raise notice '02 catch: %, %', sqlstate, sqlerrm::numeric;
+ end;
+ begin
+ raise exception alias_div_by_zero 'testing';
+ exception when division_by_zero then
+ raise notice 'Divison by zero: %, %', sqlstate, sqlerrm;
+ end;
+ return 1;
+ end; $$ language plpgsql;
+ select innerfx();
+ ERROR: 100.34
+ select outerfx();
+ NOTICE: 01 catch: U0001, foo
+ NOTICE: 02 catch: U1001, 100.34
+ NOTICE: Divison by zero: 22012, testing
+ outerfx
+ ---------
+ 1
+ (1 row)
+
+ drop function outerfx();
+ drop function innerfx();
Index: src/test/regress/sql/plpgsql.sql
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/test/regress/sql/plpgsql.sql,v
retrieving revision 1.33
diff -c -r1.33 plpgsql.sql
*** src/test/regress/sql/plpgsql.sql 2 Jul 2005 08:59:48 -0000 1.33
--- src/test/regress/sql/plpgsql.sql 6 Jul 2005 13:57:44 -0000
***************
*** 2280,2282 ****
--- 2280,2331 ----
end loop outer_label;
end;
$$ language plpgsql;
+
+ -- user-defined exceptions
+
+ -- should fail: illegal sqlstate for exception
+ create function innerfx() returns void as $$
+ declare
+ my_exc exception = 'U0001';
+ begin -- using msgtext as one param of exception
+ raise exception my_exc '%', CURRENT_TIMESTAMP;
+ end;
+ $$ language plpgsql;
+
+ create function innerfx() returns integer as $$
+ declare
+ my_exc1 exception = 'U1001';
+ begin -- using msgtext as one param of exception
+ raise exception my_exc1 '%', 100.34::numeric;
+ return 1;
+ end $$ language plpgsql;
+
+ create function outerfx() returns integer as $$
+ declare
+ my_excpt exception = 'U1001';
+ alias_div_by_zero exception = '22012';
+ def_sqlstate exception;
+ begin
+ begin
+ raise exception def_sqlstate 'foo';
+ exception when def_sqlstate then
+ raise notice '01 catch: %, %', sqlstate, sqlerrm;
+ end;
+ begin
+ raise notice '%', innerfx();
+ exception when my_excpt then
+ raise notice '02 catch: %, %', sqlstate, sqlerrm::numeric;
+ end;
+ begin
+ raise exception alias_div_by_zero 'testing';
+ exception when division_by_zero then
+ raise notice 'Divison by zero: %, %', sqlstate, sqlerrm;
+ end;
+ return 1;
+ end; $$ language plpgsql;
+
+ select innerfx();
+ select outerfx();
+
+ drop function outerfx();
+ drop function innerfx();
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
