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
