Thread: final light versions of Oracle compatibility (SQLSTATE, GREATEST, NEXT_DAY)
final light versions of Oracle compatibility (SQLSTATE, GREATEST, NEXT_DAY)
From
Pavel Stehule
Date:
Hello,
The first, I am sorry for my confusions. I hope this patches are
final. I recapitulate it.
1. SQLSTATE and SQLERRM exists only on exception's block, and allways
carry info about some exception.
2. Implementation of greatest and least lost function decode. Function
decode has analogy in CASE and not is implemented now.
3. ADD_MONTH is easy implemented via interval aritmetic, MONTHS_BETWEEN
are really Oracle specific, stayed out, I will be maintain these functions
in pgfoundry. NEXT_DAY and LAST_DAY are generally usefull (I hope).
Regards
Pavel Stehule
Attachment
Pavel Stehule wrote:
> 1. SQLSTATE and SQLERRM exists only on exception's block, and allways
> carry info about some exception.
Attached is a revised version of this patch. I'll apply it tonight or
tomorrow, barring any objections.
-Neil
Index: doc/src/sgml/plpgsql.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql/doc/src/sgml/plpgsql.sgml,v
retrieving revision 1.70
diff -c -r1.70 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml 7 Jun 2005 02:47:15 -0000 1.70
--- doc/src/sgml/plpgsql.sgml 9 Jun 2005 03:26:28 -0000
***************
*** 2110,2115 ****
--- 2110,2126 ----
don't use <literal>EXCEPTION</> without need.
</para>
</tip>
+
+ <para>
+ Within an exception handler, the <varname>SQLSTATE</varname>
+ variable contains the error code that corresponds to the
+ exception that was raised (refer to <xref
+ linkend="errcodes-table"> for a list of possible error
+ codes). The <varname>SQLERRM</varname> variable contains the
+ error message associated with the exception. These variables are
+ undefined outside exception handlers.
+ </para>
+
<example id="plpgsql-upsert-example">
<title>Exceptions with UPDATE/INSERT</title>
<para>
Index: src/backend/utils/error/elog.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/utils/error/elog.c,v
retrieving revision 1.158
diff -c -r1.158 elog.c
*** src/backend/utils/error/elog.c 12 Mar 2005 01:54:44 -0000 1.158
--- src/backend/utils/error/elog.c 8 Jun 2005 04:59:14 -0000
***************
*** 1451,1456 ****
--- 1451,1476 ----
}
}
+ /*
+ * Unpack MAKE_SQLSTATE code. Note that this returns a pointer to a
+ * static buffer.
+ */
+ char *
+ unpack_sql_state(int sql_state)
+ {
+ static char buf[12];
+ int i;
+
+ for (i = 0; i < 5; i++)
+ {
+ buf[i] = PGUNSIXBIT(sql_state);
+ sql_state >>= 6;
+ }
+
+ buf[i] = '\0';
+ return buf;
+ }
+
/*
* Write error report to server's log
***************
*** 1466,1486 ****
appendStringInfo(&buf, "%s: ", error_severity(edata->elevel));
if (Log_error_verbosity >= PGERROR_VERBOSE)
! {
! /* unpack MAKE_SQLSTATE code */
! char tbuf[12];
! int ssval;
! int i;
!
! ssval = edata->sqlerrcode;
! for (i = 0; i < 5; i++)
! {
! tbuf[i] = PGUNSIXBIT(ssval);
! ssval >>= 6;
! }
! tbuf[i] = '\0';
! appendStringInfo(&buf, "%s: ", tbuf);
! }
if (edata->message)
append_with_tabs(&buf, edata->message);
--- 1486,1492 ----
appendStringInfo(&buf, "%s: ", error_severity(edata->elevel));
if (Log_error_verbosity >= PGERROR_VERBOSE)
! appendStringInfo(&buf, "%s: ", unpack_sql_state(edata->sqlerrcode));
if (edata->message)
append_with_tabs(&buf, edata->message);
Index: src/include/utils/elog.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/utils/elog.h,v
retrieving revision 1.78
diff -c -r1.78 elog.h
*** src/include/utils/elog.h 31 Dec 2004 22:03:46 -0000 1.78
--- src/include/utils/elog.h 8 Jun 2005 04:59:58 -0000
***************
*** 282,287 ****
--- 282,288 ----
/* Other exported functions */
extern void DebugFileOpen(void);
+ extern char *unpack_sql_state(int sql_state);
/*
* Write errors to stderr (or by equal means when stderr is
Index: src/pl/plpgsql/src/gram.y
===================================================================
RCS file: /var/lib/cvs/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.74
diff -c -r1.74 gram.y
*** src/pl/plpgsql/src/gram.y 8 Jun 2005 00:49:36 -0000 1.74
--- src/pl/plpgsql/src/gram.y 9 Jun 2005 02:13:21 -0000
***************
*** 92,97 ****
--- 92,98 ----
PLpgSQL_stmt_block *program;
PLpgSQL_condition *condition;
PLpgSQL_exception *exception;
+ PLpgSQL_exception_block *exception_block;
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
}
***************
*** 129,137 ****
%type <stmt> stmt_dynexecute stmt_getdiag
%type <stmt> stmt_open stmt_fetch stmt_close stmt_null
! %type <list> exception_sect proc_exceptions
%type <exception> proc_exception
%type <condition> proc_conditions
%type <list> raise_params
%type <ival> raise_level raise_param
--- 130,140 ----
%type <stmt> stmt_dynexecute stmt_getdiag
%type <stmt> stmt_open stmt_fetch stmt_close stmt_null
! %type <list> proc_exceptions
! %type <exception_block> exception_sect
%type <exception> proc_exception
%type <condition> proc_conditions
+ %type <variable> sqlstate_var sqlerrm_var
%type <list> raise_params
%type <ival> raise_level raise_param
***************
*** 1495,1519 ****
;
exception_sect :
! { $$ = NIL; }
! | K_EXCEPTION proc_exceptions
! { $$ = $2; }
;
proc_exceptions : proc_exceptions proc_exception
{
$$ = lappend($1, $2);
}
| proc_exception
{
$$ = list_make1($1);
}
;
proc_exception : K_WHEN lno proc_conditions K_THEN proc_sect
{
PLpgSQL_exception *new;
new = palloc0(sizeof(PLpgSQL_exception));
new->lineno = $2;
new->conditions = $3;
--- 1498,1554 ----
;
exception_sect :
! { $$ = NULL; }
! | K_EXCEPTION sqlstate_var sqlerrm_var proc_exceptions
! {
! PLpgSQL_exception_block *new = palloc(sizeof(PLpgSQL_exception_block));
!
! new->sqlstate_varno = $2->dno;
! new->sqlerrm_varno = $3->dno;
! new->exc_list = $4;
!
! $$ = new;
! }
;
proc_exceptions : proc_exceptions proc_exception
{
+ plpgsql_ns_pop();
$$ = lappend($1, $2);
}
| proc_exception
{
+ plpgsql_ns_pop();
$$ = list_make1($1);
}
;
+ sqlstate_var : lno
+ {
+ PLpgSQL_variable *var = plpgsql_build_variable("sqlstate", $1,
+ plpgsql_build_datatype(TEXTOID, -1),
+ true);
+ ((PLpgSQL_var*) var)->isconst = true;
+ $$ = var;
+ }
+ ;
+
+ sqlerrm_var : lno
+ {
+ PLpgSQL_variable *var = plpgsql_build_variable("sqlerrm", $1,
+ plpgsql_build_datatype(TEXTOID, -1),
+ true);
+ ((PLpgSQL_var *) var)->isconst = true;
+ $$ = var;
+ }
+ ;
+
proc_exception : K_WHEN lno proc_conditions K_THEN proc_sect
{
PLpgSQL_exception *new;
+ plpgsql_ns_push(NULL);
+
new = palloc0(sizeof(PLpgSQL_exception));
new->lineno = $2;
new->conditions = $3;
Index: src/pl/plpgsql/src/pl_comp.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/pl/plpgsql/src/pl_comp.c,v
retrieving revision 1.90
diff -c -r1.90 pl_comp.c
*** src/pl/plpgsql/src/pl_comp.c 29 May 2005 04:23:06 -0000 1.90
--- src/pl/plpgsql/src/pl_comp.c 9 Jun 2005 02:03:44 -0000
***************
*** 656,662 ****
if (num_out_args > 0 || function->fn_rettype == VOIDOID ||
function->fn_retset)
{
! if (function->action->exceptions != NIL)
{
PLpgSQL_stmt_block *new;
--- 656,662 ----
if (num_out_args > 0 || function->fn_rettype == VOIDOID ||
function->fn_retset)
{
! if (function->action->exceptions != NULL)
{
PLpgSQL_stmt_block *new;
***************
*** 882,888 ****
}
/*
! * Do a lookup on the compilers namestack
*/
nse = plpgsql_ns_lookup(cp[0], NULL);
if (nse != NULL)
--- 882,888 ----
}
/*
! * Do a lookup on the compiler's namestack
*/
nse = plpgsql_ns_lookup(cp[0], NULL);
if (nse != NULL)
***************
*** 1935,1941 ****
/* ----------
* plpgsql_adddatum Add a variable, record or row
! * to the compilers datum list.
* ----------
*/
void
--- 1935,1941 ----
/* ----------
* plpgsql_adddatum Add a variable, record or row
! * to the compiler's datum list.
* ----------
*/
void
Index: src/pl/plpgsql/src/pl_exec.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.142
diff -c -r1.142 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c 7 Jun 2005 02:47:17 -0000 1.142
--- src/pl/plpgsql/src/pl_exec.c 9 Jun 2005 03:05:19 -0000
***************
*** 180,186 ****
static void exec_init_tuple_store(PLpgSQL_execstate *estate);
static bool compatible_tupdesc(TupleDesc td1, TupleDesc td2);
static void exec_set_found(PLpgSQL_execstate *estate, bool state);
!
/* ----------
* plpgsql_exec_function Called by the call handler for
--- 180,186 ----
static void exec_init_tuple_store(PLpgSQL_execstate *estate);
static bool compatible_tupdesc(TupleDesc td1, TupleDesc td2);
static void exec_set_found(PLpgSQL_execstate *estate, bool state);
! static void free_var(PLpgSQL_var *var);
/* ----------
* plpgsql_exec_function Called by the call handler for
***************
*** 760,771 ****
{
PLpgSQL_var *var = (PLpgSQL_var *) (estate->datums[n]);
! if (var->freeval)
! {
! pfree((void *) (var->value));
! var->freeval = false;
! }
!
if (!var->isconst || var->isnull)
{
if (var->default_val == NULL)
--- 760,766 ----
{
PLpgSQL_var *var = (PLpgSQL_var *) (estate->datums[n]);
! free_var(var);
if (!var->isconst || var->isnull)
{
if (var->default_val == NULL)
***************
*** 864,876 ****
SPI_restore_connection();
/* Look for a matching exception handler */
! foreach (e, block->exceptions)
{
PLpgSQL_exception *exception = (PLpgSQL_exception *) lfirst(e);
if (exception_matches_conditions(edata, exception->conditions))
{
rc = exec_stmts(estate, exception->action);
break;
}
}
--- 859,895 ----
SPI_restore_connection();
/* Look for a matching exception handler */
! foreach (e, block->exceptions->exc_list)
{
PLpgSQL_exception *exception = (PLpgSQL_exception *) lfirst(e);
if (exception_matches_conditions(edata, exception->conditions))
{
+ /*
+ * Initialize the magic SQLSTATE and SQLERRM
+ * variables for the exception block. We needn't
+ * do this until we have found a matching
+ * exception.
+ */
+ PLpgSQL_var *state_var;
+ PLpgSQL_var *errm_var;
+
+ state_var = (PLpgSQL_var *) (estate->datums[block->exceptions->sqlstate_varno]);
+ state_var->value = DirectFunctionCall1(textin,
+ CStringGetDatum(unpack_sql_state(edata->sqlerrcode)));
+ state_var->freeval = true;
+ state_var->isnull = false;
+
+ errm_var = (PLpgSQL_var *) (estate->datums[block->exceptions->sqlerrm_varno]);
+ errm_var->value = DirectFunctionCall1(textin,
+ CStringGetDatum(edata->message));
+ errm_var->freeval = true;
+ errm_var->isnull = false;
+
rc = exec_stmts(estate, exception->action);
+
+ free_var(state_var);
+ free_var(errm_var);
break;
}
}
***************
*** 2586,2594 ****
* Store the eventually assigned cursor name in the cursor variable
* ----------
*/
! if (curvar->freeval)
! pfree((void *) (curvar->value));
!
curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
curvar->isnull = false;
curvar->freeval = true;
--- 2605,2611 ----
* Store the eventually assigned cursor name in the cursor variable
* ----------
*/
! free_var(curvar);
curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
curvar->isnull = false;
curvar->freeval = true;
***************
*** 2684,2692 ****
* Store the eventually assigned portal name in the cursor variable
* ----------
*/
! if (curvar->freeval)
! pfree((void *) (curvar->value));
!
curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
curvar->isnull = false;
curvar->freeval = true;
--- 2701,2707 ----
* Store the eventually assigned portal name in the cursor variable
* ----------
*/
! free_var(curvar);
curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
curvar->isnull = false;
curvar->freeval = true;
***************
*** 2857,2867 ****
errmsg("NULL cannot be assigned to variable \"%s\" declared NOT NULL",
var->refname)));
! if (var->freeval)
! {
! pfree(DatumGetPointer(var->value));
! var->freeval = false;
! }
/*
* If type is by-reference, make sure we have a freshly
--- 2872,2878 ----
errmsg("NULL cannot be assigned to variable \"%s\" declared NOT NULL",
var->refname)));
! free_var(var);
/*
* If type is by-reference, make sure we have a freshly
***************
*** 4343,4345 ****
--- 4354,4365 ----
FreeExecutorState(simple_eval_estate);
simple_eval_estate = NULL;
}
+
+ void free_var(PLpgSQL_var *var)
+ {
+ if (var->freeval)
+ {
+ pfree(DatumGetPointer(var->value));
+ var->freeval = false;
+ }
+ }
Index: src/pl/plpgsql/src/pl_funcs.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/pl/plpgsql/src/pl_funcs.c,v
retrieving revision 1.40
diff -c -r1.40 pl_funcs.c
*** src/pl/plpgsql/src/pl_funcs.c 5 Apr 2005 06:22:16 -0000 1.40
--- src/pl/plpgsql/src/pl_funcs.c 9 Jun 2005 02:05:02 -0000
***************
*** 215,220 ****
--- 215,221 ----
{
PLpgSQL_ns *ns = ns_current;
PLpgSQL_nsitem *nse;
+ size_t name_len;
Assert(name != NULL);
***************
*** 233,242 ****
}
}
! nse = palloc(sizeof(PLpgSQL_nsitem) + strlen(name));
nse->itemtype = itemtype;
nse->itemno = itemno;
! strcpy(nse->name, name);
ns->items[ns->items_used++] = nse;
}
--- 234,245 ----
}
}
! /* sizeof(PLpgSQL_nsitem) includes space for the NUL terminator */
! name_len = strlen(name);
! nse = palloc(sizeof(PLpgSQL_nsitem) + name_len);
nse->itemtype = itemtype;
nse->itemno = itemno;
! memcpy(nse->name, name, name_len + 1);
ns->items[ns->items_used++] = nse;
}
***************
*** 634,640 ****
{
ListCell *e;
! foreach (e, block->exceptions)
{
PLpgSQL_exception *exc = (PLpgSQL_exception *) lfirst(e);
PLpgSQL_condition *cond;
--- 637,643 ----
{
ListCell *e;
! foreach (e, block->exceptions->exc_list)
{
PLpgSQL_exception *exc = (PLpgSQL_exception *) lfirst(e);
PLpgSQL_condition *cond;
Index: src/pl/plpgsql/src/plpgsql.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/pl/plpgsql/src/plpgsql.h,v
retrieving revision 1.61
diff -c -r1.61 plpgsql.h
*** src/pl/plpgsql/src/plpgsql.h 7 Jun 2005 02:47:18 -0000 1.61
--- src/pl/plpgsql/src/plpgsql.h 9 Jun 2005 02:02:53 -0000
***************
*** 323,328 ****
--- 323,335 ----
} PLpgSQL_condition;
typedef struct
+ {
+ int sqlstate_varno;
+ int sqlerrm_varno;
+ List *exc_list;
+ } PLpgSQL_exception_block;
+
+ typedef struct
{ /* One EXCEPTION ... WHEN clause */
int lineno;
PLpgSQL_condition *conditions;
***************
*** 336,344 ****
int lineno;
char *label;
List *body; /* List of statements */
- List *exceptions; /* List of WHEN clauses */
int n_initvars;
int *initvarnos;
} PLpgSQL_stmt_block;
--- 343,351 ----
int lineno;
char *label;
List *body; /* List of statements */
int n_initvars;
int *initvarnos;
+ PLpgSQL_exception_block *exceptions;
} PLpgSQL_stmt_block;
Index: src/test/regress/expected/plpgsql.out
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/expected/plpgsql.out,v
retrieving revision 1.32
diff -c -r1.32 plpgsql.out
*** src/test/regress/expected/plpgsql.out 7 Jun 2005 02:47:20 -0000 1.32
--- src/test/regress/expected/plpgsql.out 9 Jun 2005 03:34:27 -0000
***************
*** 2415,2417 ****
--- 2415,2467 ----
drop table eifoo cascade;
drop type eitype cascade;
+ --
+ -- SQLSTATE and SQLERRM test
+ --
+ -- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
+ -- blocks
+ create function excpt_test() returns void as $$
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ end; $$ language plpgsql;
+ ERROR: syntax error at or near "sqlstate" at character 79
+ LINE 3: raise notice '% %', sqlstate, sqlerrm;
+ ^
+ -- should fail
+ create function excpt_test() returns void as $$
+ begin
+ begin
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ end;
+ end;
+ end; $$ language plpgsql;
+ ERROR: syntax error at or near "sqlstate" at character 108
+ LINE 5: raise notice '% %', sqlstate, sqlerrm;
+ ^
+ create function excpt_test() returns void as $$
+ begin
+ begin
+ raise exception 'user exception';
+ exception when others then
+ raise notice 'caught exception % %', sqlstate, sqlerrm;
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ perform 10/0;
+ exception when others then
+ raise notice 'caught exception % %', sqlstate, sqlerrm;
+ end;
+ raise notice '% %', sqlstate, sqlerrm;
+ end;
+ end; $$ language plpgsql;
+ select excpt_test();
+ NOTICE: caught exception P0001 user exception
+ NOTICE: P0001 user exception
+ NOTICE: caught exception 22012 division by zero
+ NOTICE: P0001 user exception
+ excpt_test
+ ------------
+
+ (1 row)
+
+ drop function excpt_test();
Index: src/test/regress/sql/plpgsql.sql
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/sql/plpgsql.sql,v
retrieving revision 1.27
diff -c -r1.27 plpgsql.sql
*** src/test/regress/sql/plpgsql.sql 7 Jun 2005 02:47:23 -0000 1.27
--- src/test/regress/sql/plpgsql.sql 9 Jun 2005 03:06:02 -0000
***************
*** 2050,2052 ****
--- 2050,2092 ----
drop table eifoo cascade;
drop type eitype cascade;
+
+ --
+ -- SQLSTATE and SQLERRM test
+ --
+
+ -- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
+ -- blocks
+ create function excpt_test() returns void as $$
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ end; $$ language plpgsql;
+
+ -- should fail
+ create function excpt_test() returns void as $$
+ begin
+ begin
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ end;
+ end;
+ end; $$ language plpgsql;
+
+ create function excpt_test() returns void as $$
+ begin
+ begin
+ raise exception 'user exception';
+ exception when others then
+ raise notice 'caught exception % %', sqlstate, sqlerrm;
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ perform 10/0;
+ exception when others then
+ raise notice 'caught exception % %', sqlstate, sqlerrm;
+ end;
+ raise notice '% %', sqlstate, sqlerrm;
+ end;
+ end; $$ language plpgsql;
+
+ select excpt_test();
+ drop function excpt_test();
Neil Conway <neilc@samurai.com> writes:
> Attached is a revised version of this patch. I'll apply it tonight or
> tomorrow, barring any objections.
I still find the grammar changes to be an ugly kluge --- it should be
possible to do this without introducing bogus nonterminals.
The ns push/pop operations don't appear to be correctly matched
(consider multiple WHEN clauses, a case the regression test does not
cover), nor do they surround the places where the variables are created.
It is likely that you don't need a push/pop at all; if it appears to
work now it's because the end of the block results in a pop and so
the variables disappear then anyway.
The patch is sloppy about whether free_var() is static or not.
I find the proposed change in plpgsql_ns_additem a distinct
disimprovement --- it's dubious even as a micro-optimization
and it certainly hurts legibility.
regards, tom lane
Also, do we want these features? Do they duplicate anything we already have? --------------------------------------------------------------------------- Tom Lane wrote: > Neil Conway <neilc@samurai.com> writes: > > Attached is a revised version of this patch. I'll apply it tonight or > > tomorrow, barring any objections. > > I still find the grammar changes to be an ugly kluge --- it should be > possible to do this without introducing bogus nonterminals. > > The ns push/pop operations don't appear to be correctly matched > (consider multiple WHEN clauses, a case the regression test does not > cover), nor do they surround the places where the variables are created. > It is likely that you don't need a push/pop at all; if it appears to > work now it's because the end of the block results in a pop and so > the variables disappear then anyway. > > The patch is sloppy about whether free_var() is static or not. > > I find the proposed change in plpgsql_ns_additem a distinct > disimprovement --- it's dubious even as a micro-optimization > and it certainly hurts legibility. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Also, do we want these features? Do they duplicate anything we already
> have?
This patch only covers SQLSTATE/SQLERRM for plpgsql, which I think we're
now agreed on doing. The other stuff is still open for discussion ---
personally I'm OK with the concept of LEAST and GREATEST, not too
excited about the other things Pavel suggested. (Note I've not looked
at the proposed code for least/greatest yet, it may have issues.)
regards, tom lane
Tom Lane wrote:
> I still find the grammar changes to be an ugly kluge --- it should be
> possible to do this without introducing bogus nonterminals.
The scope-local variables need to be added to the namespace by the time
that we parse the WHEN clauses. I can see two ways to do that: adding a
bogus non-terminal, or using a mid-rule action. Mid-rule actions are
pretty ugly, though. Is there a better alternative?
> The ns push/pop operations don't appear to be correctly matched
Sorry, asleep at the switch -- the ns_push/pop stuff isn't even needed,
it was from an early revision of the patch.
A revised patch is attached -- once the nonterminal stuff is sorted I'll
apply it.
-Neil
Index: doc/src/sgml/plpgsql.sgml
===================================================================
RCS file: /var/lib/cvs/pgsql/doc/src/sgml/plpgsql.sgml,v
retrieving revision 1.70
diff -c -r1.70 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml 7 Jun 2005 02:47:15 -0000 1.70
--- doc/src/sgml/plpgsql.sgml 10 Jun 2005 01:22:04 -0000
***************
*** 2110,2115 ****
--- 2110,2126 ----
don't use <literal>EXCEPTION</> without need.
</para>
</tip>
+
+ <para>
+ Within an exception handler, the <varname>SQLSTATE</varname>
+ variable contains the error code that corresponds to the
+ exception that was raised (refer to <xref
+ linkend="errcodes-table"> for a list of possible error
+ codes). The <varname>SQLERRM</varname> variable contains the
+ error message associated with the exception. These variables are
+ undefined outside exception handlers.
+ </para>
+
<example id="plpgsql-upsert-example">
<title>Exceptions with UPDATE/INSERT</title>
<para>
Index: src/backend/utils/error/elog.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/utils/error/elog.c,v
retrieving revision 1.159
diff -c -r1.159 elog.c
*** src/backend/utils/error/elog.c 9 Jun 2005 22:29:52 -0000 1.159
--- src/backend/utils/error/elog.c 10 Jun 2005 01:22:04 -0000
***************
*** 1482,1487 ****
--- 1482,1507 ----
}
}
+ /*
+ * Unpack MAKE_SQLSTATE code. Note that this returns a pointer to a
+ * static buffer.
+ */
+ char *
+ unpack_sql_state(int sql_state)
+ {
+ static char buf[12];
+ int i;
+
+ for (i = 0; i < 5; i++)
+ {
+ buf[i] = PGUNSIXBIT(sql_state);
+ sql_state >>= 6;
+ }
+
+ buf[i] = '\0';
+ return buf;
+ }
+
/*
* Write error report to server's log
***************
*** 1497,1517 ****
appendStringInfo(&buf, "%s: ", error_severity(edata->elevel));
if (Log_error_verbosity >= PGERROR_VERBOSE)
! {
! /* unpack MAKE_SQLSTATE code */
! char tbuf[12];
! int ssval;
! int i;
!
! ssval = edata->sqlerrcode;
! for (i = 0; i < 5; i++)
! {
! tbuf[i] = PGUNSIXBIT(ssval);
! ssval >>= 6;
! }
! tbuf[i] = '\0';
! appendStringInfo(&buf, "%s: ", tbuf);
! }
if (edata->message)
append_with_tabs(&buf, edata->message);
--- 1517,1523 ----
appendStringInfo(&buf, "%s: ", error_severity(edata->elevel));
if (Log_error_verbosity >= PGERROR_VERBOSE)
! appendStringInfo(&buf, "%s: ", unpack_sql_state(edata->sqlerrcode));
if (edata->message)
append_with_tabs(&buf, edata->message);
Index: src/include/utils/elog.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/utils/elog.h,v
retrieving revision 1.78
diff -c -r1.78 elog.h
*** src/include/utils/elog.h 31 Dec 2004 22:03:46 -0000 1.78
--- src/include/utils/elog.h 10 Jun 2005 01:22:04 -0000
***************
*** 282,287 ****
--- 282,288 ----
/* Other exported functions */
extern void DebugFileOpen(void);
+ extern char *unpack_sql_state(int sql_state);
/*
* Write errors to stderr (or by equal means when stderr is
Index: src/pl/plpgsql/src/gram.y
===================================================================
RCS file: /var/lib/cvs/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.74
diff -c -r1.74 gram.y
*** src/pl/plpgsql/src/gram.y 8 Jun 2005 00:49:36 -0000 1.74
--- src/pl/plpgsql/src/gram.y 10 Jun 2005 01:22:04 -0000
***************
*** 92,97 ****
--- 92,98 ----
PLpgSQL_stmt_block *program;
PLpgSQL_condition *condition;
PLpgSQL_exception *exception;
+ PLpgSQL_exception_block *exception_block;
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
}
***************
*** 129,137 ****
%type <stmt> stmt_dynexecute stmt_getdiag
%type <stmt> stmt_open stmt_fetch stmt_close stmt_null
! %type <list> exception_sect proc_exceptions
%type <exception> proc_exception
%type <condition> proc_conditions
%type <list> raise_params
%type <ival> raise_level raise_param
--- 130,140 ----
%type <stmt> stmt_dynexecute stmt_getdiag
%type <stmt> stmt_open stmt_fetch stmt_close stmt_null
! %type <list> proc_exceptions
! %type <exception_block> exception_sect
%type <exception> proc_exception
%type <condition> proc_conditions
+ %type <variable> sqlstate_var sqlerrm_var
%type <list> raise_params
%type <ival> raise_level raise_param
***************
*** 1495,1503 ****
;
exception_sect :
! { $$ = NIL; }
! | K_EXCEPTION proc_exceptions
! { $$ = $2; }
;
proc_exceptions : proc_exceptions proc_exception
--- 1498,1514 ----
;
exception_sect :
! { $$ = NULL; }
! | K_EXCEPTION sqlstate_var sqlerrm_var proc_exceptions
! {
! PLpgSQL_exception_block *new = palloc(sizeof(PLpgSQL_exception_block));
!
! new->sqlstate_varno = $2->dno;
! new->sqlerrm_varno = $3->dno;
! new->exc_list = $4;
!
! $$ = new;
! }
;
proc_exceptions : proc_exceptions proc_exception
***************
*** 1510,1515 ****
--- 1521,1546 ----
}
;
+ sqlstate_var : lno
+ {
+ PLpgSQL_variable *var = plpgsql_build_variable("sqlstate", $1,
+ plpgsql_build_datatype(TEXTOID, -1),
+ true);
+ ((PLpgSQL_var *) var)->isconst = true;
+ $$ = var;
+ }
+ ;
+
+ sqlerrm_var : lno
+ {
+ PLpgSQL_variable *var = plpgsql_build_variable("sqlerrm", $1,
+ plpgsql_build_datatype(TEXTOID, -1),
+ true);
+ ((PLpgSQL_var *) var)->isconst = true;
+ $$ = var;
+ }
+ ;
+
proc_exception : K_WHEN lno proc_conditions K_THEN proc_sect
{
PLpgSQL_exception *new;
Index: src/pl/plpgsql/src/pl_comp.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/pl/plpgsql/src/pl_comp.c,v
retrieving revision 1.90
diff -c -r1.90 pl_comp.c
*** src/pl/plpgsql/src/pl_comp.c 29 May 2005 04:23:06 -0000 1.90
--- src/pl/plpgsql/src/pl_comp.c 10 Jun 2005 01:22:04 -0000
***************
*** 656,662 ****
if (num_out_args > 0 || function->fn_rettype == VOIDOID ||
function->fn_retset)
{
! if (function->action->exceptions != NIL)
{
PLpgSQL_stmt_block *new;
--- 656,662 ----
if (num_out_args > 0 || function->fn_rettype == VOIDOID ||
function->fn_retset)
{
! if (function->action->exceptions != NULL)
{
PLpgSQL_stmt_block *new;
***************
*** 882,888 ****
}
/*
! * Do a lookup on the compilers namestack
*/
nse = plpgsql_ns_lookup(cp[0], NULL);
if (nse != NULL)
--- 882,888 ----
}
/*
! * Do a lookup on the compiler's namestack
*/
nse = plpgsql_ns_lookup(cp[0], NULL);
if (nse != NULL)
***************
*** 1935,1941 ****
/* ----------
* plpgsql_adddatum Add a variable, record or row
! * to the compilers datum list.
* ----------
*/
void
--- 1935,1941 ----
/* ----------
* plpgsql_adddatum Add a variable, record or row
! * to the compiler's datum list.
* ----------
*/
void
Index: src/pl/plpgsql/src/pl_exec.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.142
diff -c -r1.142 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c 7 Jun 2005 02:47:17 -0000 1.142
--- src/pl/plpgsql/src/pl_exec.c 10 Jun 2005 01:22:04 -0000
***************
*** 180,186 ****
static void exec_init_tuple_store(PLpgSQL_execstate *estate);
static bool compatible_tupdesc(TupleDesc td1, TupleDesc td2);
static void exec_set_found(PLpgSQL_execstate *estate, bool state);
!
/* ----------
* plpgsql_exec_function Called by the call handler for
--- 180,186 ----
static void exec_init_tuple_store(PLpgSQL_execstate *estate);
static bool compatible_tupdesc(TupleDesc td1, TupleDesc td2);
static void exec_set_found(PLpgSQL_execstate *estate, bool state);
! static void free_var(PLpgSQL_var *var);
/* ----------
* plpgsql_exec_function Called by the call handler for
***************
*** 760,771 ****
{
PLpgSQL_var *var = (PLpgSQL_var *) (estate->datums[n]);
! if (var->freeval)
! {
! pfree((void *) (var->value));
! var->freeval = false;
! }
!
if (!var->isconst || var->isnull)
{
if (var->default_val == NULL)
--- 760,766 ----
{
PLpgSQL_var *var = (PLpgSQL_var *) (estate->datums[n]);
! free_var(var);
if (!var->isconst || var->isnull)
{
if (var->default_val == NULL)
***************
*** 864,876 ****
SPI_restore_connection();
/* Look for a matching exception handler */
! foreach (e, block->exceptions)
{
PLpgSQL_exception *exception = (PLpgSQL_exception *) lfirst(e);
if (exception_matches_conditions(edata, exception->conditions))
{
rc = exec_stmts(estate, exception->action);
break;
}
}
--- 859,895 ----
SPI_restore_connection();
/* Look for a matching exception handler */
! foreach (e, block->exceptions->exc_list)
{
PLpgSQL_exception *exception = (PLpgSQL_exception *) lfirst(e);
if (exception_matches_conditions(edata, exception->conditions))
{
+ /*
+ * Initialize the magic SQLSTATE and SQLERRM
+ * variables for the exception block. We needn't
+ * do this until we have found a matching
+ * exception.
+ */
+ PLpgSQL_var *state_var;
+ PLpgSQL_var *errm_var;
+
+ state_var = (PLpgSQL_var *) (estate->datums[block->exceptions->sqlstate_varno]);
+ state_var->value = DirectFunctionCall1(textin,
+ CStringGetDatum(unpack_sql_state(edata->sqlerrcode)));
+ state_var->freeval = true;
+ state_var->isnull = false;
+
+ errm_var = (PLpgSQL_var *) (estate->datums[block->exceptions->sqlerrm_varno]);
+ errm_var->value = DirectFunctionCall1(textin,
+ CStringGetDatum(edata->message));
+ errm_var->freeval = true;
+ errm_var->isnull = false;
+
rc = exec_stmts(estate, exception->action);
+
+ free_var(state_var);
+ free_var(errm_var);
break;
}
}
***************
*** 2586,2594 ****
* Store the eventually assigned cursor name in the cursor variable
* ----------
*/
! if (curvar->freeval)
! pfree((void *) (curvar->value));
!
curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
curvar->isnull = false;
curvar->freeval = true;
--- 2605,2611 ----
* Store the eventually assigned cursor name in the cursor variable
* ----------
*/
! free_var(curvar);
curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
curvar->isnull = false;
curvar->freeval = true;
***************
*** 2684,2692 ****
* Store the eventually assigned portal name in the cursor variable
* ----------
*/
! if (curvar->freeval)
! pfree((void *) (curvar->value));
!
curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
curvar->isnull = false;
curvar->freeval = true;
--- 2701,2707 ----
* Store the eventually assigned portal name in the cursor variable
* ----------
*/
! free_var(curvar);
curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
curvar->isnull = false;
curvar->freeval = true;
***************
*** 2857,2867 ****
errmsg("NULL cannot be assigned to variable \"%s\" declared NOT NULL",
var->refname)));
! if (var->freeval)
! {
! pfree(DatumGetPointer(var->value));
! var->freeval = false;
! }
/*
* If type is by-reference, make sure we have a freshly
--- 2872,2878 ----
errmsg("NULL cannot be assigned to variable \"%s\" declared NOT NULL",
var->refname)));
! free_var(var);
/*
* If type is by-reference, make sure we have a freshly
***************
*** 4343,4345 ****
--- 4354,4366 ----
FreeExecutorState(simple_eval_estate);
simple_eval_estate = NULL;
}
+
+ static void
+ free_var(PLpgSQL_var *var)
+ {
+ if (var->freeval)
+ {
+ pfree(DatumGetPointer(var->value));
+ var->freeval = false;
+ }
+ }
Index: src/pl/plpgsql/src/pl_funcs.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/pl/plpgsql/src/pl_funcs.c,v
retrieving revision 1.40
diff -c -r1.40 pl_funcs.c
*** src/pl/plpgsql/src/pl_funcs.c 5 Apr 2005 06:22:16 -0000 1.40
--- src/pl/plpgsql/src/pl_funcs.c 10 Jun 2005 01:22:04 -0000
***************
*** 634,640 ****
{
ListCell *e;
! foreach (e, block->exceptions)
{
PLpgSQL_exception *exc = (PLpgSQL_exception *) lfirst(e);
PLpgSQL_condition *cond;
--- 634,640 ----
{
ListCell *e;
! foreach (e, block->exceptions->exc_list)
{
PLpgSQL_exception *exc = (PLpgSQL_exception *) lfirst(e);
PLpgSQL_condition *cond;
Index: src/pl/plpgsql/src/plpgsql.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/pl/plpgsql/src/plpgsql.h,v
retrieving revision 1.61
diff -c -r1.61 plpgsql.h
*** src/pl/plpgsql/src/plpgsql.h 7 Jun 2005 02:47:18 -0000 1.61
--- src/pl/plpgsql/src/plpgsql.h 10 Jun 2005 01:22:04 -0000
***************
*** 323,328 ****
--- 323,335 ----
} PLpgSQL_condition;
typedef struct
+ {
+ int sqlstate_varno;
+ int sqlerrm_varno;
+ List *exc_list; /* List of WHEN clauses */
+ } PLpgSQL_exception_block;
+
+ typedef struct
{ /* One EXCEPTION ... WHEN clause */
int lineno;
PLpgSQL_condition *conditions;
***************
*** 336,344 ****
int lineno;
char *label;
List *body; /* List of statements */
- List *exceptions; /* List of WHEN clauses */
int n_initvars;
int *initvarnos;
} PLpgSQL_stmt_block;
--- 343,351 ----
int lineno;
char *label;
List *body; /* List of statements */
int n_initvars;
int *initvarnos;
+ PLpgSQL_exception_block *exceptions;
} PLpgSQL_stmt_block;
Index: src/test/regress/expected/plpgsql.out
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/expected/plpgsql.out,v
retrieving revision 1.32
diff -c -r1.32 plpgsql.out
*** src/test/regress/expected/plpgsql.out 7 Jun 2005 02:47:20 -0000 1.32
--- src/test/regress/expected/plpgsql.out 10 Jun 2005 01:28:51 -0000
***************
*** 2415,2417 ****
--- 2415,2471 ----
drop table eifoo cascade;
drop type eitype cascade;
+ --
+ -- SQLSTATE and SQLERRM test
+ --
+ -- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
+ -- blocks
+ create function excpt_test() returns void as $$
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ end; $$ language plpgsql;
+ ERROR: syntax error at or near "sqlstate" at character 79
+ LINE 3: raise notice '% %', sqlstate, sqlerrm;
+ ^
+ -- should fail
+ create function excpt_test() returns void as $$
+ begin
+ begin
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ end;
+ end;
+ end; $$ language plpgsql;
+ ERROR: syntax error at or near "sqlstate" at character 108
+ LINE 5: raise notice '% %', sqlstate, sqlerrm;
+ ^
+ create function excpt_test() returns void as $$
+ begin
+ begin
+ raise exception 'user exception';
+ exception when others then
+ raise notice 'caught exception % %', sqlstate, sqlerrm;
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ perform 10/0;
+ exception
+ when substring_error then
+ -- this exception handler shouldn't be invoked
+ raise notice 'unexpected exception: % %', sqlstate, sqlerrm;
+ when division_by_zero then
+ raise notice 'caught exception % %', sqlstate, sqlerrm;
+ end;
+ raise notice '% %', sqlstate, sqlerrm;
+ end;
+ end; $$ language plpgsql;
+ select excpt_test();
+ NOTICE: caught exception P0001 user exception
+ NOTICE: P0001 user exception
+ NOTICE: caught exception 22012 division by zero
+ NOTICE: P0001 user exception
+ excpt_test
+ ------------
+
+ (1 row)
+
+ drop function excpt_test();
Index: src/test/regress/sql/plpgsql.sql
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/sql/plpgsql.sql,v
retrieving revision 1.27
diff -c -r1.27 plpgsql.sql
*** src/test/regress/sql/plpgsql.sql 7 Jun 2005 02:47:23 -0000 1.27
--- src/test/regress/sql/plpgsql.sql 10 Jun 2005 01:25:03 -0000
***************
*** 2050,2052 ****
--- 2050,2096 ----
drop table eifoo cascade;
drop type eitype cascade;
+
+ --
+ -- SQLSTATE and SQLERRM test
+ --
+
+ -- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
+ -- blocks
+ create function excpt_test() returns void as $$
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ end; $$ language plpgsql;
+
+ -- should fail
+ create function excpt_test() returns void as $$
+ begin
+ begin
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ end;
+ end;
+ end; $$ language plpgsql;
+
+ create function excpt_test() returns void as $$
+ begin
+ begin
+ raise exception 'user exception';
+ exception when others then
+ raise notice 'caught exception % %', sqlstate, sqlerrm;
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ perform 10/0;
+ exception
+ when substring_error then
+ -- this exception handler shouldn't be invoked
+ raise notice 'unexpected exception: % %', sqlstate, sqlerrm;
+ when division_by_zero then
+ raise notice 'caught exception % %', sqlstate, sqlerrm;
+ end;
+ raise notice '% %', sqlstate, sqlerrm;
+ end;
+ end; $$ language plpgsql;
+
+ select excpt_test();
+ drop function excpt_test();
Neil Conway <neilc@samurai.com> writes:
> Tom Lane wrote:
>> I still find the grammar changes to be an ugly kluge --- it should be
>> possible to do this without introducing bogus nonterminals.
> The scope-local variables need to be added to the namespace by the time
> that we parse the WHEN clauses. I can see two ways to do that: adding a
> bogus non-terminal, or using a mid-rule action. Mid-rule actions are
> pretty ugly, though. Is there a better alternative?
Right, mid-rule actions were what I had in mind. They're not uglier
than introducing empty nonterminals --- and in fact plpgsql's grammar
already relies on 'em. The cursor variable declaration production
(about line 359 in CVS tip) seems to me to offer a very direct parallel
to what we want to do here.
regards, tom lane
Tom Lane wrote: > Right, mid-rule actions were what I had in mind. They're not uglier > than introducing empty nonterminals Well, IMHO they make the grammar rather hard to read when the action has multiple lines (we would need at least 6 lines of code in the mid-rule action, I believe). Unless we want two contiguous mid-rule actions (which is even _less_ readable), we'll need to futz with adding another member to %union to hold the two varnos the mid-rule action will produce. Considering that the Bison manual suggests that it implements mid-rule actions by introducing an implicit bogus non-terminal ([1]), I don't think there is likely to be a difference in performance either way, and I think mid-rule actions don't offer a notational improvement in this case. -Neil [1] http://www.gnu.org/software/bison/manual/html_mono/bison.html#Mid_002dRule-Actions, toward the end of the section
Neil Conway <neilc@samurai.com> writes:
> Considering that the Bison manual suggests that it implements
> mid-rule actions by introducing an implicit bogus non-terminal ([1]),
Indeed ... and the reason that they bothered to do that is that mid-rule
actions are more understandable ;-). A nonterminal that is not intended
to represent any real input, ever, is just plain weird.
> Unless we want two contiguous mid-rule actions
> (which is even _less_ readable), we'll need to futz with adding another
> member to %union to hold the two varnos the mid-rule action will
> produce.
Not at all. The right way to do this, I think, is for the mid-rule
action to palloc the PLpgSQL_exception_block, fill the variables into
that, and return the block as its semantic value. The end-of-rule
action then picks up the block and adds what it needs to.
One reason this is cleaner is that it scales to more SQLERRx variables
without further renumbering of the rule components.
regards, tom lane
Tom Lane wrote:
> A nonterminal that is not intended to represent any real input, ever,
> is just plain weird.
If you say so... PL/PgSQL already uses such a beast, though: the lno
nonterminal, for example.
> Not at all. The right way to do this, I think, is for the mid-rule
> action to palloc the PLpgSQL_exception_block, fill the variables into
> that, and return the block as its semantic value. The end-of-rule
> action then picks up the block and adds what it needs to.
Ah, I see -- that makes sense. Attached is a revised patch -- applied to
HEAD.
-Neil
Index: doc/src/sgml/plpgsql.sgml
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/doc/src/sgml/plpgsql.sgml,v
retrieving revision 1.70
diff -c -r1.70 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml 7 Jun 2005 02:47:15 -0000 1.70
--- doc/src/sgml/plpgsql.sgml 10 Jun 2005 15:34:04 -0000
***************
*** 2110,2115 ****
--- 2110,2126 ----
don't use <literal>EXCEPTION</> without need.
</para>
</tip>
+
+ <para>
+ Within an exception handler, the <varname>SQLSTATE</varname>
+ variable contains the error code that corresponds to the
+ exception that was raised (refer to <xref
+ linkend="errcodes-table"> for a list of possible error
+ codes). The <varname>SQLERRM</varname> variable contains the
+ error message associated with the exception. These variables are
+ undefined outside exception handlers.
+ </para>
+
<example id="plpgsql-upsert-example">
<title>Exceptions with UPDATE/INSERT</title>
<para>
Index: src/backend/utils/error/elog.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/utils/error/elog.c,v
retrieving revision 1.159
diff -c -r1.159 elog.c
*** src/backend/utils/error/elog.c 9 Jun 2005 22:29:52 -0000 1.159
--- src/backend/utils/error/elog.c 10 Jun 2005 15:34:04 -0000
***************
*** 1482,1487 ****
--- 1482,1507 ----
}
}
+ /*
+ * Unpack MAKE_SQLSTATE code. Note that this returns a pointer to a
+ * static buffer.
+ */
+ char *
+ unpack_sql_state(int sql_state)
+ {
+ static char buf[12];
+ int i;
+
+ for (i = 0; i < 5; i++)
+ {
+ buf[i] = PGUNSIXBIT(sql_state);
+ sql_state >>= 6;
+ }
+
+ buf[i] = '\0';
+ return buf;
+ }
+
/*
* Write error report to server's log
***************
*** 1497,1517 ****
appendStringInfo(&buf, "%s: ", error_severity(edata->elevel));
if (Log_error_verbosity >= PGERROR_VERBOSE)
! {
! /* unpack MAKE_SQLSTATE code */
! char tbuf[12];
! int ssval;
! int i;
!
! ssval = edata->sqlerrcode;
! for (i = 0; i < 5; i++)
! {
! tbuf[i] = PGUNSIXBIT(ssval);
! ssval >>= 6;
! }
! tbuf[i] = '\0';
! appendStringInfo(&buf, "%s: ", tbuf);
! }
if (edata->message)
append_with_tabs(&buf, edata->message);
--- 1517,1523 ----
appendStringInfo(&buf, "%s: ", error_severity(edata->elevel));
if (Log_error_verbosity >= PGERROR_VERBOSE)
! appendStringInfo(&buf, "%s: ", unpack_sql_state(edata->sqlerrcode));
if (edata->message)
append_with_tabs(&buf, edata->message);
Index: src/include/utils/elog.h
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/include/utils/elog.h,v
retrieving revision 1.78
diff -c -r1.78 elog.h
*** src/include/utils/elog.h 31 Dec 2004 22:03:46 -0000 1.78
--- src/include/utils/elog.h 10 Jun 2005 15:34:04 -0000
***************
*** 282,287 ****
--- 282,288 ----
/* Other exported functions */
extern void DebugFileOpen(void);
+ extern char *unpack_sql_state(int sql_state);
/*
* Write errors to stderr (or by equal means when stderr is
Index: src/pl/plpgsql/src/gram.y
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.74
diff -c -r1.74 gram.y
*** src/pl/plpgsql/src/gram.y 8 Jun 2005 00:49:36 -0000 1.74
--- src/pl/plpgsql/src/gram.y 10 Jun 2005 16:02:48 -0000
***************
*** 92,97 ****
--- 92,98 ----
PLpgSQL_stmt_block *program;
PLpgSQL_condition *condition;
PLpgSQL_exception *exception;
+ PLpgSQL_exception_block *exception_block;
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
}
***************
*** 129,135 ****
%type <stmt> stmt_dynexecute stmt_getdiag
%type <stmt> stmt_open stmt_fetch stmt_close stmt_null
! %type <list> exception_sect proc_exceptions
%type <exception> proc_exception
%type <condition> proc_conditions
--- 130,137 ----
%type <stmt> stmt_dynexecute stmt_getdiag
%type <stmt> stmt_open stmt_fetch stmt_close stmt_null
! %type <list> proc_exceptions
! %type <exception_block> exception_sect
%type <exception> proc_exception
%type <condition> proc_conditions
***************
*** 1495,1503 ****
;
exception_sect :
! { $$ = NIL; }
! | K_EXCEPTION proc_exceptions
! { $$ = $2; }
;
proc_exceptions : proc_exceptions proc_exception
--- 1497,1534 ----
;
exception_sect :
! { $$ = NULL; }
! | K_EXCEPTION lno
! {
! /*
! * We use a mid-rule action to add these
! * special variables to the namespace before
! * parsing the WHEN clauses themselves.
! */
! PLpgSQL_exception_block *new = palloc(sizeof(PLpgSQL_exception_block));
! PLpgSQL_variable *var;
!
! var = plpgsql_build_variable("sqlstate", $2,
! plpgsql_build_datatype(TEXTOID, -1),
! true);
! ((PLpgSQL_var *) var)->isconst = true;
! new->sqlstate_varno = var->dno;
!
! var = plpgsql_build_variable("sqlerrm", $2,
! plpgsql_build_datatype(TEXTOID, -1),
! true);
! ((PLpgSQL_var *) var)->isconst = true;
! new->sqlerrm_varno = var->dno;
!
! $<exception_block>$ = new;
! }
! proc_exceptions
! {
! PLpgSQL_exception_block *new = $<exception_block>3;
! new->exc_list = $4;
!
! $$ = new;
! }
;
proc_exceptions : proc_exceptions proc_exception
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.90
diff -c -r1.90 pl_comp.c
*** src/pl/plpgsql/src/pl_comp.c 29 May 2005 04:23:06 -0000 1.90
--- src/pl/plpgsql/src/pl_comp.c 10 Jun 2005 15:34:04 -0000
***************
*** 656,662 ****
if (num_out_args > 0 || function->fn_rettype == VOIDOID ||
function->fn_retset)
{
! if (function->action->exceptions != NIL)
{
PLpgSQL_stmt_block *new;
--- 656,662 ----
if (num_out_args > 0 || function->fn_rettype == VOIDOID ||
function->fn_retset)
{
! if (function->action->exceptions != NULL)
{
PLpgSQL_stmt_block *new;
***************
*** 882,888 ****
}
/*
! * Do a lookup on the compilers namestack
*/
nse = plpgsql_ns_lookup(cp[0], NULL);
if (nse != NULL)
--- 882,888 ----
}
/*
! * Do a lookup on the compiler's namestack
*/
nse = plpgsql_ns_lookup(cp[0], NULL);
if (nse != NULL)
***************
*** 1935,1941 ****
/* ----------
* plpgsql_adddatum Add a variable, record or row
! * to the compilers datum list.
* ----------
*/
void
--- 1935,1941 ----
/* ----------
* plpgsql_adddatum Add a variable, record or row
! * to the compiler's datum list.
* ----------
*/
void
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.142
diff -c -r1.142 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c 7 Jun 2005 02:47:17 -0000 1.142
--- src/pl/plpgsql/src/pl_exec.c 10 Jun 2005 15:34:04 -0000
***************
*** 180,186 ****
static void exec_init_tuple_store(PLpgSQL_execstate *estate);
static bool compatible_tupdesc(TupleDesc td1, TupleDesc td2);
static void exec_set_found(PLpgSQL_execstate *estate, bool state);
!
/* ----------
* plpgsql_exec_function Called by the call handler for
--- 180,186 ----
static void exec_init_tuple_store(PLpgSQL_execstate *estate);
static bool compatible_tupdesc(TupleDesc td1, TupleDesc td2);
static void exec_set_found(PLpgSQL_execstate *estate, bool state);
! static void free_var(PLpgSQL_var *var);
/* ----------
* plpgsql_exec_function Called by the call handler for
***************
*** 760,771 ****
{
PLpgSQL_var *var = (PLpgSQL_var *) (estate->datums[n]);
! if (var->freeval)
! {
! pfree((void *) (var->value));
! var->freeval = false;
! }
!
if (!var->isconst || var->isnull)
{
if (var->default_val == NULL)
--- 760,766 ----
{
PLpgSQL_var *var = (PLpgSQL_var *) (estate->datums[n]);
! free_var(var);
if (!var->isconst || var->isnull)
{
if (var->default_val == NULL)
***************
*** 864,876 ****
SPI_restore_connection();
/* Look for a matching exception handler */
! foreach (e, block->exceptions)
{
PLpgSQL_exception *exception = (PLpgSQL_exception *) lfirst(e);
if (exception_matches_conditions(edata, exception->conditions))
{
rc = exec_stmts(estate, exception->action);
break;
}
}
--- 859,895 ----
SPI_restore_connection();
/* Look for a matching exception handler */
! foreach (e, block->exceptions->exc_list)
{
PLpgSQL_exception *exception = (PLpgSQL_exception *) lfirst(e);
if (exception_matches_conditions(edata, exception->conditions))
{
+ /*
+ * Initialize the magic SQLSTATE and SQLERRM
+ * variables for the exception block. We needn't
+ * do this until we have found a matching
+ * exception.
+ */
+ PLpgSQL_var *state_var;
+ PLpgSQL_var *errm_var;
+
+ state_var = (PLpgSQL_var *) (estate->datums[block->exceptions->sqlstate_varno]);
+ state_var->value = DirectFunctionCall1(textin,
+ CStringGetDatum(unpack_sql_state(edata->sqlerrcode)));
+ state_var->freeval = true;
+ state_var->isnull = false;
+
+ errm_var = (PLpgSQL_var *) (estate->datums[block->exceptions->sqlerrm_varno]);
+ errm_var->value = DirectFunctionCall1(textin,
+ CStringGetDatum(edata->message));
+ errm_var->freeval = true;
+ errm_var->isnull = false;
+
rc = exec_stmts(estate, exception->action);
+
+ free_var(state_var);
+ free_var(errm_var);
break;
}
}
***************
*** 2586,2594 ****
* Store the eventually assigned cursor name in the cursor variable
* ----------
*/
! if (curvar->freeval)
! pfree((void *) (curvar->value));
!
curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
curvar->isnull = false;
curvar->freeval = true;
--- 2605,2611 ----
* Store the eventually assigned cursor name in the cursor variable
* ----------
*/
! free_var(curvar);
curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
curvar->isnull = false;
curvar->freeval = true;
***************
*** 2684,2692 ****
* Store the eventually assigned portal name in the cursor variable
* ----------
*/
! if (curvar->freeval)
! pfree((void *) (curvar->value));
!
curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
curvar->isnull = false;
curvar->freeval = true;
--- 2701,2707 ----
* Store the eventually assigned portal name in the cursor variable
* ----------
*/
! free_var(curvar);
curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
curvar->isnull = false;
curvar->freeval = true;
***************
*** 2857,2867 ****
errmsg("NULL cannot be assigned to variable \"%s\" declared NOT NULL",
var->refname)));
! if (var->freeval)
! {
! pfree(DatumGetPointer(var->value));
! var->freeval = false;
! }
/*
* If type is by-reference, make sure we have a freshly
--- 2872,2878 ----
errmsg("NULL cannot be assigned to variable \"%s\" declared NOT NULL",
var->refname)));
! free_var(var);
/*
* If type is by-reference, make sure we have a freshly
***************
*** 4343,4345 ****
--- 4354,4366 ----
FreeExecutorState(simple_eval_estate);
simple_eval_estate = NULL;
}
+
+ static void
+ free_var(PLpgSQL_var *var)
+ {
+ if (var->freeval)
+ {
+ pfree(DatumGetPointer(var->value));
+ var->freeval = false;
+ }
+ }
Index: src/pl/plpgsql/src/pl_funcs.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/pl/plpgsql/src/pl_funcs.c,v
retrieving revision 1.40
diff -c -r1.40 pl_funcs.c
*** src/pl/plpgsql/src/pl_funcs.c 5 Apr 2005 06:22:16 -0000 1.40
--- src/pl/plpgsql/src/pl_funcs.c 10 Jun 2005 15:34:04 -0000
***************
*** 634,640 ****
{
ListCell *e;
! foreach (e, block->exceptions)
{
PLpgSQL_exception *exc = (PLpgSQL_exception *) lfirst(e);
PLpgSQL_condition *cond;
--- 634,640 ----
{
ListCell *e;
! foreach (e, block->exceptions->exc_list)
{
PLpgSQL_exception *exc = (PLpgSQL_exception *) lfirst(e);
PLpgSQL_condition *cond;
Index: src/pl/plpgsql/src/plpgsql.h
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/pl/plpgsql/src/plpgsql.h,v
retrieving revision 1.61
diff -c -r1.61 plpgsql.h
*** src/pl/plpgsql/src/plpgsql.h 7 Jun 2005 02:47:18 -0000 1.61
--- src/pl/plpgsql/src/plpgsql.h 10 Jun 2005 15:34:04 -0000
***************
*** 323,328 ****
--- 323,335 ----
} PLpgSQL_condition;
typedef struct
+ {
+ int sqlstate_varno;
+ int sqlerrm_varno;
+ List *exc_list; /* List of WHEN clauses */
+ } PLpgSQL_exception_block;
+
+ typedef struct
{ /* One EXCEPTION ... WHEN clause */
int lineno;
PLpgSQL_condition *conditions;
***************
*** 336,344 ****
int lineno;
char *label;
List *body; /* List of statements */
- List *exceptions; /* List of WHEN clauses */
int n_initvars;
int *initvarnos;
} PLpgSQL_stmt_block;
--- 343,351 ----
int lineno;
char *label;
List *body; /* List of statements */
int n_initvars;
int *initvarnos;
+ PLpgSQL_exception_block *exceptions;
} PLpgSQL_stmt_block;
Index: src/test/regress/expected/plpgsql.out
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/test/regress/expected/plpgsql.out,v
retrieving revision 1.32
diff -c -r1.32 plpgsql.out
*** src/test/regress/expected/plpgsql.out 7 Jun 2005 02:47:20 -0000 1.32
--- src/test/regress/expected/plpgsql.out 10 Jun 2005 15:34:04 -0000
***************
*** 2415,2417 ****
--- 2415,2471 ----
drop table eifoo cascade;
drop type eitype cascade;
+ --
+ -- SQLSTATE and SQLERRM test
+ --
+ -- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
+ -- blocks
+ create function excpt_test() returns void as $$
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ end; $$ language plpgsql;
+ ERROR: syntax error at or near "sqlstate" at character 79
+ LINE 3: raise notice '% %', sqlstate, sqlerrm;
+ ^
+ -- should fail
+ create function excpt_test() returns void as $$
+ begin
+ begin
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ end;
+ end;
+ end; $$ language plpgsql;
+ ERROR: syntax error at or near "sqlstate" at character 108
+ LINE 5: raise notice '% %', sqlstate, sqlerrm;
+ ^
+ create function excpt_test() returns void as $$
+ begin
+ begin
+ raise exception 'user exception';
+ exception when others then
+ raise notice 'caught exception % %', sqlstate, sqlerrm;
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ perform 10/0;
+ exception
+ when substring_error then
+ -- this exception handler shouldn't be invoked
+ raise notice 'unexpected exception: % %', sqlstate, sqlerrm;
+ when division_by_zero then
+ raise notice 'caught exception % %', sqlstate, sqlerrm;
+ end;
+ raise notice '% %', sqlstate, sqlerrm;
+ end;
+ end; $$ language plpgsql;
+ select excpt_test();
+ NOTICE: caught exception P0001 user exception
+ NOTICE: P0001 user exception
+ NOTICE: caught exception 22012 division by zero
+ NOTICE: P0001 user exception
+ excpt_test
+ ------------
+
+ (1 row)
+
+ drop function excpt_test();
Index: src/test/regress/sql/plpgsql.sql
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/test/regress/sql/plpgsql.sql,v
retrieving revision 1.27
diff -c -r1.27 plpgsql.sql
*** src/test/regress/sql/plpgsql.sql 7 Jun 2005 02:47:23 -0000 1.27
--- src/test/regress/sql/plpgsql.sql 10 Jun 2005 15:34:04 -0000
***************
*** 2050,2052 ****
--- 2050,2096 ----
drop table eifoo cascade;
drop type eitype cascade;
+
+ --
+ -- SQLSTATE and SQLERRM test
+ --
+
+ -- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
+ -- blocks
+ create function excpt_test() returns void as $$
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ end; $$ language plpgsql;
+
+ -- should fail
+ create function excpt_test() returns void as $$
+ begin
+ begin
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ end;
+ end;
+ end; $$ language plpgsql;
+
+ create function excpt_test() returns void as $$
+ begin
+ begin
+ raise exception 'user exception';
+ exception when others then
+ raise notice 'caught exception % %', sqlstate, sqlerrm;
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ perform 10/0;
+ exception
+ when substring_error then
+ -- this exception handler shouldn't be invoked
+ raise notice 'unexpected exception: % %', sqlstate, sqlerrm;
+ when division_by_zero then
+ raise notice 'caught exception % %', sqlstate, sqlerrm;
+ end;
+ raise notice '% %', sqlstate, sqlerrm;
+ end;
+ end; $$ language plpgsql;
+
+ select excpt_test();
+ drop function excpt_test();