Hello,
I changed code by your and Neil's notes. The name SQLCODE isn't well,
better is SQLSTATE. It's very similar DB2 variable. I am not sure, so my
solutions is best. Propably not. It's only particular solution for
plpgsql. The files plpgsql.sql and plpgsql.out are diffs for regression
tests.
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.
Regards
Pavel Stehule
*** test.old/plpgsql.sql 2005-02-22 08:18:27.000000000 +0100
--- test/plpgsql.sql 2005-03-08 09:58:23.419281208 +0100
***************
*** 1917,1920 ****
create function void_return_expr() returns void as $$
begin
return 5;
! end;$$ language plpgsql;
--- 1917,1944 ----
create function void_return_expr() returns void as $$
begin
return 5;
! end;$$ language plpgsql;
!
! --
! -- Test of built variables SQLERRM and SQLSTATE
! --
!
! 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();
!
!
*** test.old/plpgsql.out 2005-02-22 08:18:25.000000000 +0100
--- test/plpgsql.out 2005-03-08 09:56:58.272225528 +0100
***************
*** 2242,2244 ****
--- 2242,2252 ----
ERROR: function returning void cannot specify RETURN expression at or near
"5" at character 72
LINE 3: return 5;
^
+ CREATE FUNCTION
+ psql:plpgsql.sql:1942: NOTICE: P0001 first exception
+ psql:plpgsql.sql:1942: NOTICE: 000000 Sucessful completion
+ psql:plpgsql.sql:1942: NOTICE: P0001 last exception
+ trap_exceptions
+ -----------------
+
+ (1 row)
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-08 09:22:20.886036232 +0100
***************
*** 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 */
***************
*** 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->sqlstate_varno =
$2.sqlstate_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("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
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-08 09:40:16.537512352 +0100
***************
*** 181,186 ****
--- 181,187 ----
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);
/* ----------
***************
*** 809,814 ****
--- 810,829 ----
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
*/
***************
*** 918,923 ****
--- 933,949 ----
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,
* it will have left us in a disconnected state. We
need this
***************
*** 4319,4321 ****
--- 4345,4368 ----
FreeExecutorState(simple_eval_estate);
simple_eval_estate = NULL;
}
+
+ /*
+ * unpack MAKE_SQLSTATE code
+ * This code is redundand backend/utils/error/elog.c. I din't
+ * wont modify different part than plpgsql
+ */
+
+ 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;
+ }
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-08 08:59:46.877876760 +0100
***************
*** 339,344 ****
--- 339,346 ----
List *exceptions; /* List of WHEN clauses */
int n_initvars;
int *initvarnos;
+ int sqlstate_varno;
+ int sqlerrm_varno;
} PLpgSQL_stmt_block;
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
