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]

Reply via email to