Re: final light versions of Oracle compatibility (SQLSTATE, GREATEST, - Mailing list pgsql-patches

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();