Hello,

I used different format now. Documentation:

This patch is implementation of variables SQLERRM and SQLCODE for plpgsql 
language. Variable SQLCODE 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 SQLCODE and 'Sucessful completion' for SQLERRM. Some example 
of using is in file test.sql. 

Regards

Pavel Stehule
--
-- Test of built variables SQLERRM and SQLCODE
--

create or replace function trap_exceptions() returns void as $_$
begin
  begin
    raise exception 'first exception';
  exception when others then
    raise notice '% %', SQLCODE, SQLERRM;
  end;
  raise notice '% %', SQLCODE, SQLERRM;
  begin
    raise exception 'last exception';
  exception when others then
    raise notice '% %', SQLCODE, SQLERRM;
  end;
  return;
end; $_$ language plpgsql;

select trap_exceptions();

drop function trap_exceptions();

CREATE FUNCTION
NOTICE:  P0001 first exception
NOTICE:  000000 Sucessfull completation
NOTICE:  P0001 last exception
 trap_exceptions 
-----------------
 
(1 row)

DROP FUNCTION
diff -c -r src.old/gram.y src/gram.y
*** src.old/gram.y      2005-02-22 08:18:24.000000000 +0100
--- src/gram.y  2005-03-07 10:05:29.286336064 +0100
***************
*** 80,85 ****
--- 80,90 ----
                        int  n_initvars;
                        int  *initvarnos;
                }                                               declhdr;
+                 struct 
+               {
+                       int sqlcode_varno;
+                       int sqlerrm_varno;
+               }                                               fict_vars;
                List                                    *list;
                PLpgSQL_type                    *dtype;
                PLpgSQL_datum                   *scalar;        /* a VAR, 
RECFIELD, or TRIGARG */
***************
*** 95,101 ****
                PLpgSQL_nsitem                  *nsitem;
                PLpgSQL_diag_item               *diagitem;
  }
! 
  %type <declhdr> decl_sect
  %type <varname> decl_varname
  %type <str>           decl_renname
--- 100,106 ----
                PLpgSQL_nsitem                  *nsitem;
                PLpgSQL_diag_item               *diagitem;
  }
! %type <fict_vars> fict_vars_sect
  %type <declhdr> decl_sect
  %type <varname> decl_varname
  %type <str>           decl_renname
***************
*** 244,268 ****
                                | ';'
                                ;
  
! 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();
  
                                                $$ = (PLpgSQL_stmt *)new;
                                        }
                                ;
  
  
  decl_sect             : opt_label
--- 249,288 ----
                                | ';'
                                ;
  
! 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->sqlcode_varno = 
$2.sqlcode_varno;
!                                               new->sqlerrm_varno = 
$2.sqlerrm_varno;
  
                                                plpgsql_ns_pop();
  
                                                $$ = (PLpgSQL_stmt *)new;
                                        }
                                ;
+ fict_vars_sect            :
+                                       {
+                                               plpgsql_ns_setlocal(false);
+                                               PLpgSQL_variable        *var;
+                                               var = 
plpgsql_build_variable(strdup("sqlcode"), 0,
+                                                                            
plpgsql_build_datatype(TEXTOID, -1), true);  
+                                               $$.sqlcode_varno = var->dno;
+                                               var = 
plpgsql_build_variable(strdup("sqlerrm"), 0,
+                                                                            
plpgsql_build_datatype(TEXTOID, -1), true);  
+                                               $$.sqlerrm_varno = var->dno;
+                                               plpgsql_add_initdatums(NULL);
+                                       };
  
  
  decl_sect             : opt_label
diff -c -r src.old/pl_exec.c src/pl_exec.c
*** src.old/pl_exec.c   2005-02-24 02:11:40.000000000 +0100
--- src/pl_exec.c       2005-03-07 09:53:52.630243888 +0100
***************
*** 809,814 ****
--- 809,828 ----
        int                     i;
        int                     n;
  
+       /* setup SQLCODE and SQLERRM */
+       PLpgSQL_var *var;
+  
+       var = (PLpgSQL_var *) (estate->datums[block->sqlcode_varno]);
+       var->isnull = false;
+       var->freeval = false;
+       var->value = DirectFunctionCall1(textin, CStringGetDatum("00000"));
+  
+       var = (PLpgSQL_var *) (estate->datums[block->sqlerrm_varno]);
+       var->isnull = false;
+       var->freeval = false;
+       var->value = DirectFunctionCall1(textin, CStringGetDatum("Sucessful 
completion"));
+ 
+ 
        /*
         * First initialize all variables declared in this block
         */
***************
*** 918,923 ****
--- 932,957 ----
                        MemoryContextSwitchTo(oldcontext);
                        CurrentResourceOwner = oldowner;
  
+ 
+                       /* set SQLCODE and SQLERRM variables */
+                       /* 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';
+                       var = (PLpgSQL_var *) 
(estate->datums[block->sqlcode_varno]);
+                       var->value = DirectFunctionCall1(textin, 
CStringGetDatum(tbuf));
+  
+                       var = (PLpgSQL_var *) 
(estate->datums[block->sqlerrm_varno]);
+                       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
diff -c -r src.old/plpgsql.h src/plpgsql.h
*** src.old/plpgsql.h   2005-02-22 08:18:24.000000000 +0100
--- src/plpgsql.h       2005-03-07 09:39:25.613050536 +0100
***************
*** 339,344 ****
--- 339,346 ----
        List       *exceptions;         /* List of WHEN clauses */
        int                     n_initvars;
        int                *initvarnos;
+       int             sqlcode_varno;
+         int           sqlerrm_varno;
  } PLpgSQL_stmt_block;
  
  
---------------------------(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