Patch applied.  I added a documentation mention too.

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

Pavel Stehule wrote:
> Hello,
> 
> I updated patch to last changes plpgsql code. Patch contains changes for 
> gram.y, pl_exec.c, plpgsql.h, regress/sql/plpgsql.sql and 
> regress/output/plpgsql.out. I can't to write documenation, my english is 
> terrible.
> 
> This patch is implementation of variables SQLERRM and SQLSTATE for plpgsql 
> language. Variable SQLSTATE contains five chars PostgreSQL Error Code, 
> SQLERRM contains relevant message last catched exception. All variables 
> are attached to plpgsql_block and have local scope. Default values are 
> '00000' for SQLSTATE and 'Sucessful completion' for SQLERRM. 
> 
> Best regards
> Pavel Stehule

Content-Description: 

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq

-- 
  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.67
diff -c -c -r1.67 plpgsql.sgml
*** doc/src/sgml/plpgsql.sgml   19 Apr 2005 03:55:43 -0000      1.67
--- doc/src/sgml/plpgsql.sgml   26 May 2005 00:10:59 -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> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </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> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>
Index: src/pl/plpgsql/src/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.69
diff -c -c -r1.69 gram.y
*** src/pl/plpgsql/src/gram.y   7 Apr 2005 14:53:04 -0000       1.69
--- src/pl/plpgsql/src/gram.y   26 May 2005 00:11:06 -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,291 ----
                                        }
                                ;
  
+ 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.138
diff -c -c -r1.138 pl_exec.c
*** src/pl/plpgsql/src/pl_exec.c        6 May 2005 17:24:55 -0000       1.138
--- src/pl/plpgsql/src/pl_exec.c        26 May 2005 00:11:08 -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("Sucessful 
completion"));
+ 
        /*
         * First initialize all variables declared in this block
         */
***************
*** 855,860 ****
--- 870,885 ----
                        RollbackAndReleaseCurrentSubTransaction();
                        MemoryContextSwitchTo(oldcontext);
                        CurrentResourceOwner = oldowner;
+  
+                       /* set SQLSTATE and SQLERRM variables */
+                   
+                       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,
***************
*** 919,924 ****
--- 944,969 ----
        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.58
diff -c -c -r1.58 plpgsql.h
*** src/pl/plpgsql/src/plpgsql.h        5 Apr 2005 06:22:16 -0000       1.58
--- src/pl/plpgsql/src/plpgsql.h        26 May 2005 00:11:08 -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.28
diff -c -c -r1.28 plpgsql.out
*** src/test/regress/expected/plpgsql.out       7 Apr 2005 14:53:04 -0000       
1.28
--- src/test/regress/expected/plpgsql.out       26 May 2005 00:11:10 -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 or replace 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 Sucessful 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.23
diff -c -c -r1.23 plpgsql.sql
*** src/test/regress/sql/plpgsql.sql    7 Apr 2005 14:53:04 -0000       1.23
--- src/test/regress/sql/plpgsql.sql    26 May 2005 00:11:11 -0000
***************
*** 2018,2020 ****
--- 2018,2040 ----
  
  drop function void_return_expr();
  drop function missing_return_expr();
+ -- test SQLSTATE and SQLERRM
+ create or replace 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();
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match

Reply via email to