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