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 **** ! By default, any error occurring in a 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 BEGIN block with an ! EXCEPTION clause. The syntax is an extension of the ! normal syntax for a BEGIN block: <<label>> --- 2007,2019 ---- ! Any error occurring in PL/pgSQL sets variables ! SQLSTATE and 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 BEGIN block with an EXCEPTION ! clause. The syntax is an extension of the normal syntax for a ! BEGIN block: <<label>> 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_sect %type decl_sect %type decl_varname %type 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();