Hello

I changed implementation of SQLSTATE from block scope variables on 
function scope variables. I am not sure if it's moust elegant solution, 
but is very similar Oracle behavior (there is session scope variable)

CREATE OR REPLACE FUNCTION ff() RETURNS void AS $$
BEGIN
  RAISE NOTICE 'No exception: % %', SQLSTATE, SQLERRM;
  DECLARE x integer;
  BEGIN
    x := 10/0;
    EXCEPTION WHEN OTHERS THEN
    BEGIN
      RAISE NOTICE 'Div: % %', SQLSTATE, SQLERRM;
        BEGIN
          RAISE NOTICE 'Div2: % %', SQLSTATE, SQLERRM;
          RAISE EXCEPTION 'My user exception';
        EXCEPTION WHEN OTHERS THEN
          RAISE NOTICE 'User: % %', SQLSTATE, SQLERRM;
        END;
        RAISE NOTICE '1: % %', SQLSTATE, SQLERRM;
    END;
    RAISE NOTICE '2: % %', SQLSTATE, SQLERRM;
  END;
  RAISE NOTICE '3: % %', SQLSTATE, SQLERRM;
END;
$$ LANGUAGE plpgsql;
select ff();


NOTICE:  No exception: 00000 Successful completion
NOTICE:  Div: 22012 division by zero
NOTICE:  Div2: 22012 division by zero
NOTICE:  User: P0001 My user exception
NOTICE:  1: 00000 Successful completion
NOTICE:  2: 00000 Successful completion
NOTICE:  3: 00000 Successful completion

Any comments?

Regards
Pavel Stehule


diff -c -r --new-file pgsql.old/src/pl/plpgsql/src/pl_comp.c 
pgsql.new/src/pl/plpgsql/src/pl_comp.c
*** pgsql.old/src/pl/plpgsql/src/pl_comp.c      2005-05-06 19:24:55.000000000 
+0200
--- pgsql.new/src/pl/plpgsql/src/pl_comp.c      2005-05-30 07:41:25.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,645 ----
        function->fn_readonly = (procStruct->provolatile != 
PROVOLATILE_VOLATILE);
  
        /*
!        * Create the magic FOUND, SQLSTATE and SQLERRM variables.
         */
        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);
+       function->sqlstate_varno = var->dno;
+       var = plpgsql_build_variable("sqlerrm", 0,
+                                                                
plpgsql_build_datatype(TEXTOID, -1),
+                                                                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.new/src/pl/plpgsql/src/pl_exec.c
*** pgsql.old/src/pl/plpgsql/src/pl_exec.c      2005-05-06 19:24:55.000000000 
+0200
--- pgsql.new/src/pl/plpgsql/src/pl_exec.c      2005-05-30 08:55:09.000000000 
+0200
***************
*** 181,186 ****
--- 181,189 ----
  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, bool init);
+ 
  
  /* ----------
   * plpgsql_exec_function      Called by the call handler for
***************
*** 275,281 ****
         * Set the magic variable FOUND to false
         */
        exec_set_found(&estate, false);
! 
        /*
         * Now call the toplevel block of statements
         */
--- 278,284 ----
         * Set the magic variable FOUND to false
         */
        exec_set_found(&estate, false);
!       exec_reset_sqlstate(&estate, true);
        /*
         * Now call the toplevel block of statements
         */
***************
*** 845,850 ****
--- 848,854 ----
                {
                        ErrorData       *edata;
                        ListCell        *e;
+                       PLpgSQL_var *var;
  
                        /* Save error info */
                        MemoryContextSwitchTo(oldcontext);
***************
*** 855,860 ****
--- 859,872 ----
                        RollbackAndReleaseCurrentSubTransaction();
                        MemoryContextSwitchTo(oldcontext);
                        CurrentResourceOwner = oldowner;
+                       
+                       var = (PLpgSQL_var *) 
(estate->datums[estate->sqlstate_varno]);
+                       pfree((void *) (var->value));
+                       var->value = DirectFunctionCall1(textin, 
CStringGetDatum(unpack_sql_state(edata->sqlerrcode)));
+                       
+                       var = (PLpgSQL_var *) 
(estate->datums[estate->sqlerrm_varno]);
+                       pfree((void *) (var->value));
+                       var->value = DirectFunctionCall1(textin, 
CStringGetDatum(edata->message));
  
                        /*
                         * If AtEOSubXact_SPI() popped any SPI context of the 
subxact,
***************
*** 874,885 ****
                                        break;
                                }
                        }
  
                        /* If no match found, re-throw the error */
                        if (e == NULL)
                                ReThrowError(edata);
                        else
!                               FreeErrorData(edata);
                }
                PG_END_TRY();
        }
--- 886,899 ----
                                        break;
                                }
                        }
+                       exec_reset_sqlstate(estate, false); /* Oracle comp. */
  
                        /* If no match found, re-throw the error */
                        if (e == NULL)
                                ReThrowError(edata);
                        else
!                               FreeErrorData(edata);
!                       //              
                }
                PG_END_TRY();
        }
***************
*** 919,924 ****
--- 933,964 ----
        return PLPGSQL_RC_OK;
  }
  
+ static void
+ exec_reset_sqlstate(PLpgSQL_execstate *estate, bool init)
+ {
+     /* setup SQLSTATE and SQLERRM */
+   PLpgSQL_var *sqlstate, *sqlerrm;
+ 
+   sqlstate = (PLpgSQL_var *) (estate->datums[estate->sqlstate_varno]);
+   sqlerrm = (PLpgSQL_var *) (estate->datums[estate->sqlerrm_varno]);  
+ 
+   if (!init) 
+   {
+       pfree((void *) (sqlstate->value));
+       pfree((void *) (sqlerrm->value));
+   }
+  
+   sqlstate->isnull = false;
+   sqlstate->freeval = true;
+   sqlstate->value = DirectFunctionCall1(textin, CStringGetDatum("00000"));
+   
+   sqlerrm->isnull = false;
+   sqlerrm->freeval = true;
+   sqlerrm->value = DirectFunctionCall1(textin, CStringGetDatum("Successful 
completion"));
+ }
+ 
+ 
+ 
  
  /* ----------
   * exec_stmts                 Iterate over a list of statements
***************
*** 1993,1998 ****
--- 2033,2041 ----
        estate->trig_argv = NULL;
  
        estate->found_varno = func->found_varno;
+       estate->sqlstate_varno = func->sqlstate_varno;
+       estate->sqlerrm_varno = func->sqlerrm_varno;
+ 
        estate->ndatums = func->ndatums;
        estate->datums = palloc(sizeof(PLpgSQL_datum *) * estate->ndatums);
        /* caller is expected to fill the datums array */
***************
*** 4318,4320 ****
--- 4361,4382 ----
                FreeExecutorState(simple_eval_estate);
        simple_eval_estate = NULL;
  }
+ 
+ /* 
+  * 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;
+ }
diff -c -r --new-file pgsql.old/src/pl/plpgsql/src/plpgsql.h 
pgsql.new/src/pl/plpgsql/src/plpgsql.h
*** pgsql.old/src/pl/plpgsql/src/plpgsql.h      2005-04-05 08:22:16.000000000 
+0200
--- pgsql.new/src/pl/plpgsql/src/plpgsql.h      2005-05-30 07:39:32.000000000 
+0200
***************
*** 583,588 ****
--- 583,590 ----
        int                     tg_nargs_varno;
  
        int                     ndatums;
+   int sqlstate_varno;
+   int sqlerrm_varno;
        PLpgSQL_datum **datums;
        PLpgSQL_stmt_block *action;
  } PLpgSQL_function;
***************
*** 624,629 ****
--- 626,633 ----
        PLpgSQL_function *err_func; /* current func */
        PLpgSQL_stmt *err_stmt;         /* current stmt */
        const char *err_text;           /* additional state info */
+   int sqlstate_varno;
+   int sqlerrm_varno;
  } PLpgSQL_execstate;
  
  
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

Reply via email to