Hello
This patch respect last Tom's Lane notes. Is based on first variante,
http://archives.postgresql.org/pgsql-committers/2005-05/msg00303.php
SQLSTATE and SQLERRM are local variables blocks with EXCEPTION clause.
I wonted respect a litlle bit Oracle behavior and therefore exist these
variables on function scope too, allways "00000" and "Successful
completation" (allways marked isconst).
I am really not sure what is better, respect Oracle or not with visibility
SQLSTATE and SQLERRM on function scope. Is true, using these variables
outside EXCEPTION is nonsens, but on other hand all magic variables has
function scope: FOUND, NEW, OLD, ... . SQLSTATE and SQLERRM are little bit
similar. If are you in oportunity, please correct this patch. It's easy.
Clean all parts with these on functions related procedures. Then is
neccesery change documentation, add info about outside exceptions blocks
is impossible use SQLSTATE and SQLERRM variables. (regress too)
Regards
Pavel Stehule
create function excpt_test() returns void as $$
begin
raise notice '% %', sqlstate, sqlerrm;
begin
raise exception 'usr exception';
exception when others then
raise notice 'caught usr exception % %', sqlstate, sqlerrm;
begin
raise notice '% %', sqlstate, sqlerrm;
perform 10/0;
exception when others then
raise notice 'caught usr exception % %', sqlstate, sqlerrm;
end;
raise notice '% %', sqlstate, sqlerrm;
end;
raise notice '% %', sqlstate, sqlerrm;
end; $$ language plpgsql;
CREATE FUNCTION
select excpt_test();
NOTICE: 00000 Successful completion
NOTICE: caught usr exception P0001 usr exception
NOTICE: P0001 usr exception
NOTICE: caught usr exception 22012 division by zero
NOTICE: P0001 usr exception
NOTICE: 00000 Successful completion
excpt_test
------------
Regards
Pavel Stehule
diff -c -r --new-file pgsql.old/doc/src/sgml/plpgsql.sgml
pgsql/doc/src/sgml/plpgsql.sgml
*** pgsql.old/doc/src/sgml/plpgsql.sgml 2005-06-06 15:29:00.000000000 +0200
--- pgsql/doc/src/sgml/plpgsql.sgml 2005-06-06 20:46:00.000000000 +0200
***************
*** 2096,2101 ****
--- 2096,2116 ----
contains <literal>Tom Jones</> not <literal>Joe Jones</>.
</para>
+ <para>You can use variables <literal>SQLSTATE</literal> and
<literal>SQLERRM</literal>
+ for detection type and message of exception (expecially in
<literal>EXCEPTION WHEN OTHERS</literal>
+ block. <literal>SQLSTATE</literal> contains five numbers exception's code,
+ <literal>SQLSTATE</literal> contains text of exception's message. Outside
+ block with <literal>EXCEPTION</literal> clause <literal>SQLSTATE</literal>
+ and <literal>SQLERRM</literal> are "00000" and "Successfull
completation".
+ </para>
+
+ <note>
+ <para>Values of <literal>SQLSTATE</literal> are not compatible with
ORACLE
+ <literal>SQLSTATE</literal> values.
+ </para>
+ </note>
+
+
<tip>
<para>
A block containing an <literal>EXCEPTION</> clause is significantly
diff -c -r --new-file pgsql.old/src/pl/plpgsql/src/gram.y
pgsql/src/pl/plpgsql/src/gram.y
*** pgsql.old/src/pl/plpgsql/src/gram.y 2005-06-06 15:29:53.000000000 +0200
--- pgsql/src/pl/plpgsql/src/gram.y 2005-06-06 20:30:30.000000000 +0200
***************
*** 80,85 ****
--- 80,91 ----
int n_initvars;
int *initvarnos;
} declhdr;
+ struct
+ {
+ int sqlstate_varno;
+ int sqlerrm_varno;
+ List *proc_exceptions;
+ }
exception_with_vars;
List *list;
PLpgSQL_type *dtype;
PLpgSQL_datum *scalar; /* a VAR,
RECFIELD, or TRIGARG */
***************
*** 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
--- 135,142 ----
%type <stmt> stmt_dynexecute stmt_getdiag
%type <stmt> stmt_open stmt_fetch stmt_close stmt_null
! %type <exception_with_vars> exception_sect fict_vars_sect
! %type <list> proc_exceptions
%type <exception> proc_exception
%type <condition> proc_conditions
***************
*** 256,262 ****
new->n_initvars = $1.n_initvars;
new->initvarnos = $1.initvarnos;
new->body = $4;
! new->exceptions = $5;
plpgsql_ns_pop();
--- 263,272 ----
new->n_initvars = $1.n_initvars;
new->initvarnos = $1.initvarnos;
new->body = $4;
!
! new->exceptions =
$5.proc_exceptions;
! new->sqlstate_varno =
$5.sqlstate_varno;
! new->sqlerrm_varno =
$5.sqlerrm_varno;
plpgsql_ns_pop();
***************
*** 1453,1463 ****
;
exception_sect :
! { $$ = NIL; }
! | K_EXCEPTION proc_exceptions
! { $$ = $2; }
;
proc_exceptions : proc_exceptions proc_exception
{
$$ = lappend($1, $2);
--- 1463,1496 ----
;
exception_sect :
! { $$.proc_exceptions = NIL; }
! | K_EXCEPTION fict_vars_sect proc_exceptions
! {
! $$.proc_exceptions = $3;
! $$.sqlstate_varno =
$2.sqlstate_varno;
! $$.sqlerrm_varno =
$2.sqlerrm_varno;
! }
;
+ fict_vars_sect :
+ {
+ PLpgSQL_variable *var;
+ plpgsql_ns_setlocal(false);
+
+ var =
plpgsql_build_variable("sqlstate", 0,
+
plpgsql_build_datatype(TEXTOID, -1), true);
+ ((PLpgSQL_var *) var)->isconst
= true;
+ $$.sqlstate_varno = var->dno;
+
+ var =
plpgsql_build_variable("sqlerrm", 0,
+
plpgsql_build_datatype(TEXTOID, -1), true);
+ ((PLpgSQL_var *) var)->isconst
= true;
+ $$.sqlerrm_varno = var->dno;
+ }
+ ;
+
+
+
proc_exceptions : proc_exceptions proc_exception
{
$$ = lappend($1, $2);
diff -c -r --new-file pgsql.old/src/pl/plpgsql/src/pl_comp.c
pgsql/src/pl/plpgsql/src/pl_comp.c
*** pgsql.old/src/pl/plpgsql/src/pl_comp.c 2005-06-06 15:29:53.000000000
+0200
--- pgsql/src/pl/plpgsql/src/pl_comp.c 2005-06-06 19:34:17.000000000 +0200
***************
*** 622,634 ****
function->fn_readonly = (procStruct->provolatile !=
PROVOLATILE_VOLATILE);
/*
! * Create the magic FOUND variable.
*/
var = plpgsql_build_variable("found", 0,
plpgsql_build_datatype(BOOLOID, -1),
true);
function->found_varno = var->dno;
/*
* Forget about the above created variables
*/
--- 622,646 ----
function->fn_readonly = (procStruct->provolatile !=
PROVOLATILE_VOLATILE);
/*
! * Create the magic FOUND variable and SQLSTATE and SQLERRM vars
*/
var = plpgsql_build_variable("found", 0,
plpgsql_build_datatype(BOOLOID, -1),
true);
function->found_varno = var->dno;
+ var = plpgsql_build_variable("sqlstate", 0,
+
plpgsql_build_datatype(TEXTOID, -1),
+ true);
+ ((PLpgSQL_var *) var)->isconst = true;
+ function->sqlstate_varno = var->dno;
+ var = plpgsql_build_variable("sqlerrm", 0,
+
plpgsql_build_datatype(TEXTOID, -1),
+ true);
+ ((PLpgSQL_var *) var)->isconst = true;
+ function->sqlerrm_varno = var->dno;
+
+
/*
* Forget about the above created variables
*/
diff -c -r --new-file pgsql.old/src/pl/plpgsql/src/pl_exec.c
pgsql/src/pl/plpgsql/src/pl_exec.c
*** pgsql.old/src/pl/plpgsql/src/pl_exec.c 2005-06-06 15:29:53.000000000
+0200
--- pgsql/src/pl/plpgsql/src/pl_exec.c 2005-06-06 20:35:33.000000000 +0200
***************
*** 180,185 ****
--- 180,189 ----
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 char *unpack_sql_state(int ssval);
+ static void exec_reset_sqlstate(PLpgSQL_execstate *estate, int
sqlstate_varno,
+ int sqlerrm_varno);
+
/* ----------
***************
*** 272,281 ****
}
/*
! * Set the magic variable FOUND to false
*/
exec_set_found(&estate, false);
!
/*
* Now call the toplevel block of statements
*/
--- 276,285 ----
}
/*
! * Set the magic variable FOUND to false SQLSTATE 0000 and SQLERRM
Successfull ...
*/
exec_set_found(&estate, false);
! exec_reset_sqlstate(&estate,func->sqlstate_varno, func->sqlerrm_varno);
/*
* Now call the toplevel block of statements
*/
***************
*** 536,544 ****
}
/*
! * Set the magic variable FOUND to false
*/
exec_set_found(&estate, false);
/*
* Now call the toplevel block of statements
--- 540,549 ----
}
/*
! * Set the magic variable FOUND to false, SQLSTATE 0000 and SQLERRM
Successfull ...
*/
exec_set_found(&estate, false);
+ exec_reset_sqlstate(&estate,func->sqlstate_varno, func->sqlerrm_varno);
/*
* Now call the toplevel block of statements
***************
*** 747,752 ****
--- 752,775 ----
int i;
int n;
+ /* setup SQLSTATE and SQLERRM really values visible only in exception
blok */
+
+ PLpgSQL_var *var;
+
+ if (block->sqlstate_varno && block->sqlerrm_varno)
+ {
+ var = (PLpgSQL_var *) (estate->datums[block->sqlstate_varno]);
+ var->isnull = false;
+ var->freeval = true;
+ var->value = DirectFunctionCall1(textin, CStringGetDatum("00000"));
+
+ var = (PLpgSQL_var *) (estate->datums[block->sqlerrm_varno]);
+ var->isnull = false;
+ var->freeval = true;
+ var->value = DirectFunctionCall1(textin,
CStringGetDatum("Successful completion"));
+ }
+
+
/*
* First initialize all variables declared in this block
*/
***************
*** 856,861 ****
--- 879,893 ----
MemoryContextSwitchTo(oldcontext);
CurrentResourceOwner = oldowner;
+
+ var = (PLpgSQL_var *)
(estate->datums[block->sqlstate_varno]);
+ pfree((void *) (var->value));
+ var->value = DirectFunctionCall1(textin,
CStringGetDatum(unpack_sql_state(edata->sqlerrcode)));
+
+ var = (PLpgSQL_var *)
(estate->datums[block->sqlerrm_varno]);
+ pfree((void *) (var->value));
+ var->value = DirectFunctionCall1(textin,
CStringGetDatum(edata->message));
+
/*
* If AtEOSubXact_SPI() popped any SPI context of the
subxact,
* it will have left us in a disconnected state. We
need this
***************
*** 2005,2010 ****
--- 2037,2043 ----
estate->err_func = func;
estate->err_stmt = NULL;
estate->err_text = NULL;
+
}
/* ----------
***************
*** 4282,4287 ****
--- 4315,4361 ----
var->isnull = false;
}
+ static void
+ exec_reset_sqlstate(PLpgSQL_execstate *estate, int sqlstate_varno, int
sqlerrm_varno)
+ {
+ /* setup SQLSTATE and SQLERRM */
+ PLpgSQL_var *var;
+
+ var = (PLpgSQL_var *) (estate->datums[sqlstate_varno]);
+ var->isnull = false;
+ var->freeval = true;
+ var->value = DirectFunctionCall1(textin, CStringGetDatum("00000"));
+
+ var = (PLpgSQL_var *) (estate->datums[sqlerrm_varno]);
+ var->isnull = false;
+ var->freeval = true;
+ var->value = DirectFunctionCall1(textin, CStringGetDatum("Successful
completion"));
+
+ }
+
+ /*
+ * unpack MAKE_SQLSTATE code
+ * This code is copied from backend/utils/error/elog.c.
+ */
+
+ static char *
+ unpack_sql_state(int ssval)
+ {
+ static char tbuf[12];
+ int i;
+
+ for (i = 0; i < 5; i++)
+ {
+ tbuf[i] = PGUNSIXBIT(ssval);
+ ssval >>= 6;
+ }
+ tbuf[i] = '\0';
+
+ return tbuf;
+ }
+
+
+
/*
* plpgsql_xact_cb --- post-transaction-commit-or-abort cleanup
*
diff -c -r --new-file pgsql.old/src/pl/plpgsql/src/plpgsql.h
pgsql/src/pl/plpgsql/src/plpgsql.h
*** pgsql.old/src/pl/plpgsql/src/plpgsql.h 2005-06-06 15:29:53.000000000
+0200
--- pgsql/src/pl/plpgsql/src/plpgsql.h 2005-06-06 20:20:43.000000000 +0200
***************
*** 339,344 ****
--- 339,346 ----
List *exceptions; /* List of WHEN clauses */
int n_initvars;
int *initvarnos;
+ int sqlstate_varno;
+ int sqlerrm_varno;
} PLpgSQL_stmt_block;
***************
*** 583,588 ****
--- 585,593 ----
int tg_nargs_varno;
int ndatums;
+ int sqlstate_varno;
+ int sqlerrm_varno;
+
PLpgSQL_datum **datums;
PLpgSQL_stmt_block *action;
} PLpgSQL_function;
diff -c -r --new-file pgsql.old/src/test/regress/expected/plpgsql.out
pgsql/src/test/regress/expected/plpgsql.out
*** pgsql.old/src/test/regress/expected/plpgsql.out 2005-06-06
15:30:06.000000000 +0200
--- pgsql/src/test/regress/expected/plpgsql.out 2005-06-06 20:48:57.000000000
+0200
***************
*** 2380,2382 ****
--- 2380,2417 ----
CONTEXT: PL/pgSQL function "missing_return_expr"
drop function void_return_expr();
drop function missing_return_expr();
+ --
+ -- SQLSTATE and SQLERRM test
+ --
+ create function excpt_test() returns void as $$
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ begin
+ raise exception 'usr exception';
+ exception when others then
+ raise notice 'caught usr exception % %', sqlstate, sqlerrm;
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ perform 10/0;
+ exception when others then
+ raise notice 'caught usr exception % %', sqlstate, sqlerrm;
+ end;
+ raise notice '% %', sqlstate, sqlerrm;
+ end;
+ raise notice '% %', sqlstate, sqlerrm;
+ end; $$ language plpgsql;
+ CREATE FUNCTION
+ select excpt_test();
+ NOTICE: 00000 Successful completion
+ NOTICE: caught usr exception P0001 usr exception
+ NOTICE: P0001 usr exception
+ NOTICE: caught usr exception 22012 division by zero
+ NOTICE: P0001 usr exception
+ NOTICE: 00000 Successful completion
+ excpt_test
+ ------------
+
+ (1 row)
+
+ drop function excpt_test();
+ DROP FUNCTION
diff -c -r --new-file pgsql.old/src/test/regress/sql/plpgsql.sql
pgsql/src/test/regress/sql/plpgsql.sql
*** pgsql.old/src/test/regress/sql/plpgsql.sql 2005-06-06 15:30:03.000000000
+0200
--- pgsql/src/test/regress/sql/plpgsql.sql 2005-06-06 20:41:53.000000000
+0200
***************
*** 2018,2020 ****
--- 2018,2045 ----
drop function void_return_expr();
drop function missing_return_expr();
+
+ --
+ -- SQLSTATE and SQLERRM test
+ --
+ create function excpt_test() returns void as $$
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ begin
+ raise exception 'usr exception';
+ exception when others then
+ raise notice 'caught usr exception % %', sqlstate, sqlerrm;
+ begin
+ raise notice '% %', sqlstate, sqlerrm;
+ perform 10/0;
+ exception when others then
+ raise notice 'caught usr exception % %', sqlstate, sqlerrm;
+ end;
+ raise notice '% %', sqlstate, sqlerrm;
+ end;
+ raise notice '% %', sqlstate, sqlerrm;
+ end; $$ language plpgsql;
+
+ select excpt_test();
+
+ drop function excpt_test();
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
