Re: [COMMITTERS] pgsql: Add PL/pgSQL SQLSTATE and SQLERRM support - Mailing list pgsql-patches

Patch backed out, and new combined version attached.

---------------------------------------------------------------------------

Tom Lane wrote:
> Neil Conway <neilc@samurai.com> writes:
> > Tom Lane wrote:
> >> Alternatively we could make them local to any block that contains an
> >> EXCEPTION clause, which would fix point 3 and also go a long way towards
> >> addressing the unnecessary-overhead gripe.  However that would mean that
> >> an attempt to reference them from outside an exception handler would
> >> probably fail outright, rather than deliver either NULLs or
> >> 00000/"Successful completion".
>
> > This behavior sounds fine to me.
>
> I think the key distinction between this proposal and my other one
> (that SQLSTATE/SQLERRM be procedure-local) is whether you want the error
> status to be available to code that immediately follows the BEGIN block
> containing the exception handler.  That is, consider code like
>
>     BEGIN
>         -- do something perilous
>     EXCEPTION
>         WHEN OTHERS THEN -- nothing much
>     END;
>     IF SQLSTATE = '42000' THEN ...
>
> At the moment I don't have a strong opinion about this.  It seems
> closely analogous to the question whether a loop iteration variable
> should remain defined after the loop exits --- you can find cases
> where that's handy, but you can also argue it shouldn't be used.
> plpgsql itself is schizophrenic on the point (see integer versus
> record FOR-loops), which means we don't have a solid precedent to go by.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

--
  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
Index: doc/src/sgml/plpgsql.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v
retrieving revision 1.69
diff -c -c -r1.69 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml    26 May 2005 04:08:31 -0000    1.69
--- doc/src/sgml/plpgsql.sgml    26 May 2005 04:09:42 -0000
***************
*** 2007,2018 ****
      </indexterm>

      <para>
!      By default, any error occurring in a <application>PL/pgSQL</>
!      function aborts execution of the function, and indeed of the
!      surrounding transaction as well.  You can trap errors and recover
!      from them by using a <command>BEGIN</> block with an
!      <literal>EXCEPTION</> clause.  The syntax is an extension of the
!      normal syntax for a <command>BEGIN</> block:

  <synopsis>
  <optional> <<<replaceable>label</replaceable>>> </optional>
--- 2007,2019 ----
      </indexterm>

      <para>
!      Any error occurring in <application>PL/pgSQL</> sets variables
!      <varname>SQLSTATE</> and <varname>SQLERRM</>, and, by default,
!      aborts execution of the function, and indeed of the surrounding
!      transaction as well. You can trap errors and recover from them by
!      using a <command>BEGIN</> block with an <literal>EXCEPTION</>
!      clause. The syntax is an extension of the normal syntax for a
!      <command>BEGIN</> block:

  <synopsis>
  <optional> <<<replaceable>label</replaceable>>> </optional>
Index: src/pl/plpgsql/src/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.72
diff -c -c -r1.72 gram.y
*** src/pl/plpgsql/src/gram.y    26 May 2005 04:08:31 -0000    1.72
--- src/pl/plpgsql/src/gram.y    26 May 2005 04:09:44 -0000
***************
*** 80,85 ****
--- 80,90 ----
              int  n_initvars;
              int  *initvarnos;
          }                        declhdr;
+         struct
+         {
+             int sqlstate_varno;
+             int sqlerrm_varno;
+         }                        fict_vars;
          List                    *list;
          PLpgSQL_type            *dtype;
          PLpgSQL_datum            *scalar;    /* a VAR, RECFIELD, or TRIGARG */
***************
*** 96,101 ****
--- 101,107 ----
          PLpgSQL_diag_item        *diagitem;
  }

+ %type <fict_vars> fict_vars_sect
  %type <declhdr> decl_sect
  %type <varname> decl_varname
  %type <str>        decl_renname
***************
*** 244,262 ****
                  | ';'
                  ;

! pl_block        : decl_sect K_BEGIN lno proc_sect exception_sect K_END
                      {
                          PLpgSQL_stmt_block *new;

                          new = palloc0(sizeof(PLpgSQL_stmt_block));

                          new->cmd_type    = PLPGSQL_STMT_BLOCK;
!                         new->lineno        = $3;
                          new->label        = $1.label;
                          new->n_initvars = $1.n_initvars;
                          new->initvarnos = $1.initvarnos;
!                         new->body        = $4;
!                         new->exceptions    = $5;

                          plpgsql_ns_pop();

--- 250,271 ----
                  | ';'
                  ;

! pl_block        : decl_sect fict_vars_sect K_BEGIN lno proc_sect exception_sect K_END
                      {
                          PLpgSQL_stmt_block *new;

                          new = palloc0(sizeof(PLpgSQL_stmt_block));

                          new->cmd_type    = PLPGSQL_STMT_BLOCK;
!                         new->lineno        = $4;
                          new->label        = $1.label;
                          new->n_initvars = $1.n_initvars;
                          new->initvarnos = $1.initvarnos;
!                         new->body        = $5;
!                         new->exceptions    = $6;
!
!                         new->sqlstate_varno = $2.sqlstate_varno;
!                         new->sqlerrm_varno = $2.sqlerrm_varno;

                          plpgsql_ns_pop();

***************
*** 264,269 ****
--- 273,292 ----
                      }
                  ;

+ fict_vars_sect    :
+                     {
+                         PLpgSQL_variable    *var;
+
+                         plpgsql_ns_setlocal(false);
+                         var = plpgsql_build_variable("sqlstate", 0,
+                                          plpgsql_build_datatype(TEXTOID, -1), true);
+                         $$.sqlstate_varno = var->dno;
+                         var = plpgsql_build_variable("sqlerrm", 0,
+                                          plpgsql_build_datatype(TEXTOID, -1), true);
+                         $$.sqlerrm_varno = var->dno;
+                         plpgsql_add_initdatums(NULL);
+                     }
+                 ;

  decl_sect        : opt_label
                      {
Index: src/pl/plpgsql/src/pl_exec.c
===================================================================
RCS file: /cvsroot/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.141
diff -c -c -r1.141 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c    26 May 2005 04:08:31 -0000    1.141
--- src/pl/plpgsql/src/pl_exec.c    26 May 2005 04:09:46 -0000
***************
*** 180,185 ****
--- 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 char *unpack_sql_state(int ssval);


  /* ----------
***************
*** 747,752 ****
--- 748,767 ----
      int            i;
      int            n;

+
+       /* setup SQLSTATE and SQLERRM */
+       PLpgSQL_var *var;
+
+       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
       */
***************
*** 762,768 ****

                      if (var->freeval)
                      {
!                         pfree((void *) (var->value));
                          var->freeval = false;
                      }

--- 777,783 ----

                      if (var->freeval)
                      {
!                         pfree(DatumGetPointer(var->value));
                          var->freeval = false;
                      }

***************
*** 855,860 ****
--- 870,884 ----
              RollbackAndReleaseCurrentSubTransaction();
              MemoryContextSwitchTo(oldcontext);
              CurrentResourceOwner = oldowner;
+
+             /* set SQLSTATE and SQLERRM variables */
+             var = (PLpgSQL_var *) (estate->datums[block->sqlstate_varno]);
+             pfree(DatumGetPointer(var->value));
+             var->value = DirectFunctionCall1(textin, CStringGetDatum(unpack_sql_state(edata->sqlerrcode)));
+
+             var = (PLpgSQL_var *) (estate->datums[block->sqlerrm_varno]);
+             pfree(DatumGetPointer(var->value));
+             var->value = DirectFunctionCall1(textin, CStringGetDatum(edata->message));

              /*
               * If AtEOSubXact_SPI() popped any SPI context of the subxact,
***************
*** 919,924 ****
--- 943,968 ----
      return PLPGSQL_RC_OK;
  }

+ /*
+  * 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;
+ }
+
+

  /* ----------
   * exec_stmts            Iterate over a list of statements
Index: src/pl/plpgsql/src/plpgsql.h
===================================================================
RCS file: /cvsroot/pgsql/src/pl/plpgsql/src/plpgsql.h,v
retrieving revision 1.60
diff -c -c -r1.60 plpgsql.h
*** src/pl/plpgsql/src/plpgsql.h    26 May 2005 04:08:31 -0000    1.60
--- src/pl/plpgsql/src/plpgsql.h    26 May 2005 04:09:46 -0000
***************
*** 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;


--- 336,346 ----
      int            lineno;
      char       *label;
      List       *body;            /* List of statements */
!     List       *exceptions;     /* List of WHEN clauses */
!     int         n_initvars;
!     int        *initvarnos;
!     int         sqlstate_varno;
!     int         sqlerrm_varno;
  } PLpgSQL_stmt_block;


Index: src/test/regress/expected/plpgsql.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/plpgsql.out,v
retrieving revision 1.31
diff -c -c -r1.31 plpgsql.out
*** src/test/regress/expected/plpgsql.out    26 May 2005 04:08:31 -0000    1.31
--- src/test/regress/expected/plpgsql.out    26 May 2005 04:09:48 -0000
***************
*** 2380,2382 ****
--- 2380,2408 ----
  CONTEXT:  PL/pgSQL function "missing_return_expr"
  drop function void_return_expr();
  drop function missing_return_expr();
+ -- test SQLSTATE and SQLERRM
+ create function trap_exceptions() returns void as $_$
+ begin
+    begin
+      raise exception 'first exception';
+    exception when others then
+      raise notice '% %', SQLSTATE, SQLERRM;
+    end;
+    raise notice '% %', SQLSTATE, SQLERRM;
+    begin
+      raise exception 'last exception';
+    exception when others then
+      raise notice '% %', SQLSTATE, SQLERRM;
+    end;
+    return;
+ end; $_$ language plpgsql;
+ select trap_exceptions();
+ NOTICE:  P0001 first exception
+ NOTICE:  00000 Successful completion
+ NOTICE:  P0001 last exception
+  trap_exceptions
+ -----------------
+
+ (1 row)
+
+ drop function trap_exceptions();
Index: src/test/regress/sql/plpgsql.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/plpgsql.sql,v
retrieving revision 1.26
diff -c -c -r1.26 plpgsql.sql
*** src/test/regress/sql/plpgsql.sql    26 May 2005 04:08:32 -0000    1.26
--- src/test/regress/sql/plpgsql.sql    26 May 2005 04:09:49 -0000
***************
*** 2018,2020 ****
--- 2018,2041 ----

  drop function void_return_expr();
  drop function missing_return_expr();
+
+ -- test SQLSTATE and SQLERRM
+ create function trap_exceptions() returns void as $_$
+ begin
+    begin
+      raise exception 'first exception';
+    exception when others then
+      raise notice '% %', SQLSTATE, SQLERRM;
+    end;
+    raise notice '% %', SQLSTATE, SQLERRM;
+    begin
+      raise exception 'last exception';
+    exception when others then
+      raise notice '% %', SQLSTATE, SQLERRM;
+    end;
+    return;
+ end; $_$ language plpgsql;
+
+ select trap_exceptions();
+
+ drop function trap_exceptions();