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
diff -c -r pgsql.old/src/pl/plpgsql/src/gram.y pgsql/src/pl/plpgsql/src/gram.y
*** pgsql.old/src/pl/plpgsql/src/gram.y 2005-04-07 16:53:04.000000000 +0200
--- pgsql/src/pl/plpgsql/src/gram.y 2005-04-19 14:50:19.000000000 +0200
***************
*** 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,290 ----
}
;
+ 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 pgsql.old/src/pl/plpgsql/src/pl_exec.c
pgsql/src/pl/plpgsql/src/pl_exec.c
*** pgsql.old/src/pl/plpgsql/src/pl_exec.c 2005-04-07 16:53:04.000000000
+0200
--- pgsql/src/pl/plpgsql/src/pl_exec.c 2005-04-19 14:58:04.000000000 +0200
***************
*** 179,184 ****
--- 179,185 ----
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);
/* ----------
***************
*** 746,751 ****
--- 747,766 ----
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
*/
***************
*** 854,859 ****
--- 869,884 ----
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,
***************
*** 918,923 ****
--- 943,970 ----
return PLPGSQL_RC_OK;
}
+ /*
+ * 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;
+ }
+
+
/* ----------
* exec_stmts Iterate over a list of statements
diff -c -r pgsql.old/src/pl/plpgsql/src/plpgsql.h
pgsql/src/pl/plpgsql/src/plpgsql.h
*** pgsql.old/src/pl/plpgsql/src/plpgsql.h 2005-04-05 08:22:16.000000000
+0200
--- pgsql/src/pl/plpgsql/src/plpgsql.h 2005-04-19 14:59:26.000000000 +0200
***************
*** 339,344 ****
--- 339,347 ----
List *exceptions; /* List of WHEN clauses */
int n_initvars;
int *initvarnos;
+ int sqlstate_varno;
+ int sqlerrm_varno;
+
} PLpgSQL_stmt_block;
diff -c -r pgsql.old/src/test/regress/expected/plpgsql.out
pgsql/src/test/regress/expected/plpgsql.out
*** pgsql.old/src/test/regress/expected/plpgsql.out 2005-04-07
16:53:04.000000000 +0200
--- pgsql/src/test/regress/expected/plpgsql.out 2005-04-19 16:53:38.000000000
+0200
***************
*** 2380,2382 ****
--- 2380,2407 ----
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: 000000 Sucessful completion
+ NOTICE: P0001 last exception
+ trap_exceptions
+ -----------------
+
+ (1 row)
+ drop function trap_exceptions();
diff -c -r pgsql.old/src/test/regress/sql/plpgsql.sql
pgsql/src/test/regress/sql/plpgsql.sql
*** pgsql.old/src/test/regress/sql/plpgsql.sql 2005-04-07 16:53:04.000000000
+0200
--- pgsql/src/test/regress/sql/plpgsql.sql 2005-04-19 16:53:42.000000000
+0200
***************
*** 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 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
