Skip to content

Commit d46bc44

Browse files
author
Neil Conway
committed
Implement two new special variables in PL/PgSQL: SQLSTATE and SQLERRM.
These contain the SQLSTATE and error message of the current exception, respectively. They are scope-local variables that are only defined in exception handlers (so attempting to reference them outside an exception handler is an error). Update the regression tests and the documentation. Also, do some minor related cleanup: export an unpack_sql_state() function from the backend and use it to unpack a SQLSTATE into a string, and add a free_var() function to pl_exec.c Original patch from Pavel Stehule, review by Neil Conway.
1 parent 1a61896 commit d46bc44

File tree

10 files changed

+226
-51
lines changed

10 files changed

+226
-51
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.70 2005/06/07 02:47:15 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.71 2005/06/10 16:23:09 neilc Exp $
33
-->
44

55
<chapter id="plpgsql">
@@ -2110,6 +2110,17 @@ END;
21102110
don't use <literal>EXCEPTION</> without need.
21112111
</para>
21122112
</tip>
2113+
2114+
<para>
2115+
Within an exception handler, the <varname>SQLSTATE</varname>
2116+
variable contains the error code that corresponds to the
2117+
exception that was raised (refer to <xref
2118+
linkend="errcodes-table"> for a list of possible error
2119+
codes). The <varname>SQLERRM</varname> variable contains the
2120+
error message associated with the exception. These variables are
2121+
undefined outside exception handlers.
2122+
</para>
2123+
21132124
<example id="plpgsql-upsert-example">
21142125
<title>Exceptions with UPDATE/INSERT</title>
21152126
<para>

‎src/backend/utils/error/elog.c

Lines changed: 22 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -42,7 +42,7 @@
4242
*
4343
*
4444
* IDENTIFICATION
45-
* $PostgreSQL: pgsql/src/backend/utils/error/elog.c,v 1.159 2005/06/09 22:29:52 momjian Exp $
45+
* $PostgreSQL: pgsql/src/backend/utils/error/elog.c,v 1.160 2005/06/10 16:23:10 neilc Exp $
4646
*
4747
*-------------------------------------------------------------------------
4848
*/
@@ -1482,6 +1482,26 @@ log_line_prefix(StringInfo buf)
14821482
}
14831483
}
14841484

1485+
/*
1486+
* Unpack MAKE_SQLSTATE code. Note that this returns a pointer to a
1487+
* static buffer.
1488+
*/
1489+
char *
1490+
unpack_sql_state(int sql_state)
1491+
{
1492+
static char buf[12];
1493+
int i;
1494+
1495+
for (i = 0; i < 5; i++)
1496+
{
1497+
buf[i] = PGUNSIXBIT(sql_state);
1498+
sql_state >>= 6;
1499+
}
1500+
1501+
buf[i] = '\0';
1502+
return buf;
1503+
}
1504+
14851505

14861506
/*
14871507
* Write error report to server's log
@@ -1497,21 +1517,7 @@ send_message_to_server_log(ErrorData *edata)
14971517
appendStringInfo(&buf, "%s: ", error_severity(edata->elevel));
14981518

14991519
if (Log_error_verbosity >= PGERROR_VERBOSE)
1500-
{
1501-
/* unpack MAKE_SQLSTATE code */
1502-
char tbuf[12];
1503-
int ssval;
1504-
int i;
1505-
1506-
ssval = edata->sqlerrcode;
1507-
for (i = 0; i < 5; i++)
1508-
{
1509-
tbuf[i] = PGUNSIXBIT(ssval);
1510-
ssval >>= 6;
1511-
}
1512-
tbuf[i] = '\0';
1513-
appendStringInfo(&buf, "%s: ", tbuf);
1514-
}
1520+
appendStringInfo(&buf, "%s: ", unpack_sql_state(edata->sqlerrcode));
15151521

15161522
if (edata->message)
15171523
append_with_tabs(&buf, edata->message);

‎src/include/utils/elog.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/utils/elog.h,v 1.78 2004/12/31 22:03:46 pgsql Exp $
10+
* $PostgreSQL: pgsql/src/include/utils/elog.h,v 1.79 2005/06/10 16:23:10 neilc Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -282,6 +282,7 @@ extern int Log_destination;
282282

283283
/* Other exported functions */
284284
extern void DebugFileOpen(void);
285+
extern char *unpack_sql_state(int sql_state);
285286

286287
/*
287288
* Write errors to stderr (or by equal means when stderr is

‎src/pl/plpgsql/src/gram.y

Lines changed: 36 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
* procedural language
55
*
66
* IDENTIFICATION
7-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.74 2005/06/08 00:49:36 neilc Exp $
7+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.75 2005/06/10 16:23:11 neilc Exp $
88
*
99
* This software is copyrighted by Jan Wieck - Hamburg.
1010
*
@@ -92,6 +92,7 @@ static void plpgsql_sql_error_callback(void *arg);
9292
PLpgSQL_stmt_block *program;
9393
PLpgSQL_condition *condition;
9494
PLpgSQL_exception *exception;
95+
PLpgSQL_exception_block *exception_block;
9596
PLpgSQL_nsitem *nsitem;
9697
PLpgSQL_diag_item *diagitem;
9798
}
@@ -129,7 +130,8 @@ static void plpgsql_sql_error_callback(void *arg);
129130
%type <stmt> stmt_dynexecute stmt_getdiag
130131
%type <stmt> stmt_open stmt_fetch stmt_close stmt_null
131132

132-
%type <list> exception_sect proc_exceptions
133+
%type <list> proc_exceptions
134+
%type <exception_block> exception_sect
133135
%type <exception> proc_exception
134136
%type <condition> proc_conditions
135137

@@ -1495,9 +1497,38 @@ execsql_start : T_WORD
14951497
;
14961498

14971499
exception_sect :
1498-
{ $$ = NIL; }
1499-
| K_EXCEPTION proc_exceptions
1500-
{ $$ = $2; }
1500+
{ $$ = NULL; }
1501+
| K_EXCEPTION lno
1502+
{
1503+
/*
1504+
* We use a mid-rule action to add these
1505+
* special variables to the namespace before
1506+
* parsing the WHEN clauses themselves.
1507+
*/
1508+
PLpgSQL_exception_block *new = palloc(sizeof(PLpgSQL_exception_block));
1509+
PLpgSQL_variable *var;
1510+
1511+
var = plpgsql_build_variable("sqlstate", $2,
1512+
plpgsql_build_datatype(TEXTOID, -1),
1513+
true);
1514+
((PLpgSQL_var *) var)->isconst = true;
1515+
new->sqlstate_varno = var->dno;
1516+
1517+
var = plpgsql_build_variable("sqlerrm", $2,
1518+
plpgsql_build_datatype(TEXTOID, -1),
1519+
true);
1520+
((PLpgSQL_var *) var)->isconst = true;
1521+
new->sqlerrm_varno = var->dno;
1522+
1523+
$<exception_block>$ = new;
1524+
}
1525+
proc_exceptions
1526+
{
1527+
PLpgSQL_exception_block *new = $<exception_block>3;
1528+
new->exc_list = $4;
1529+
1530+
$$ = new;
1531+
}
15011532
;
15021533

15031534
proc_exceptions : proc_exceptions proc_exception

‎src/pl/plpgsql/src/pl_comp.c

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* procedural language
44
*
55
* IDENTIFICATION
6-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.90 2005/05/29 04:23:06 tgl Exp $
6+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.91 2005/06/10 16:23:11 neilc Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -656,7 +656,7 @@ do_compile(FunctionCallInfo fcinfo,
656656
if (num_out_args > 0 || function->fn_rettype == VOIDOID ||
657657
function->fn_retset)
658658
{
659-
if (function->action->exceptions != NIL)
659+
if (function->action->exceptions != NULL)
660660
{
661661
PLpgSQL_stmt_block *new;
662662

@@ -882,7 +882,7 @@ plpgsql_parse_word(char *word)
882882
}
883883

884884
/*
885-
* Do a lookup on the compilers namestack
885+
* Do a lookup on the compiler's namestack
886886
*/
887887
nse = plpgsql_ns_lookup(cp[0], NULL);
888888
if (nse != NULL)
@@ -1935,7 +1935,7 @@ plpgsql_parse_err_condition(char *condname)
19351935

19361936
/* ----------
19371937
* plpgsql_adddatum Add a variable, record or row
1938-
* to the compilers datum list.
1938+
* to the compiler's datum list.
19391939
* ----------
19401940
*/
19411941
void

‎src/pl/plpgsql/src/pl_exec.c

Lines changed: 41 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* procedural language
44
*
55
* IDENTIFICATION
6-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.142 2005/06/07 02:47:17 neilc Exp $
6+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.143 2005/06/10 16:23:11 neilc Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -180,7 +180,7 @@ static Datum exec_simple_cast_value(Datum value, Oid valtype,
180180
static void exec_init_tuple_store(PLpgSQL_execstate *estate);
181181
static bool compatible_tupdesc(TupleDesc td1, TupleDesc td2);
182182
static void exec_set_found(PLpgSQL_execstate *estate, bool state);
183-
183+
static void free_var(PLpgSQL_var *var);
184184

185185
/* ----------
186186
* plpgsql_exec_function Called by the call handler for
@@ -760,12 +760,7 @@ exec_stmt_block(PLpgSQL_execstate *estate, PLpgSQL_stmt_block *block)
760760
{
761761
PLpgSQL_var *var = (PLpgSQL_var *) (estate->datums[n]);
762762

763-
if (var->freeval)
764-
{
765-
pfree((void *) (var->value));
766-
var->freeval = false;
767-
}
768-
763+
free_var(var);
769764
if (!var->isconst || var->isnull)
770765
{
771766
if (var->default_val == NULL)
@@ -864,13 +859,37 @@ exec_stmt_block(PLpgSQL_execstate *estate, PLpgSQL_stmt_block *block)
864859
SPI_restore_connection();
865860

866861
/* Look for a matching exception handler */
867-
foreach (e, block->exceptions)
862+
foreach (e, block->exceptions->exc_list)
868863
{
869864
PLpgSQL_exception *exception = (PLpgSQL_exception *) lfirst(e);
870865

871866
if (exception_matches_conditions(edata, exception->conditions))
872867
{
868+
/*
869+
* Initialize the magic SQLSTATE and SQLERRM
870+
* variables for the exception block. We needn't
871+
* do this until we have found a matching
872+
* exception.
873+
*/
874+
PLpgSQL_var *state_var;
875+
PLpgSQL_var *errm_var;
876+
877+
state_var = (PLpgSQL_var *) (estate->datums[block->exceptions->sqlstate_varno]);
878+
state_var->value = DirectFunctionCall1(textin,
879+
CStringGetDatum(unpack_sql_state(edata->sqlerrcode)));
880+
state_var->freeval = true;
881+
state_var->isnull = false;
882+
883+
errm_var = (PLpgSQL_var *) (estate->datums[block->exceptions->sqlerrm_varno]);
884+
errm_var->value = DirectFunctionCall1(textin,
885+
CStringGetDatum(edata->message));
886+
errm_var->freeval = true;
887+
errm_var->isnull = false;
888+
873889
rc = exec_stmts(estate, exception->action);
890+
891+
free_var(state_var);
892+
free_var(errm_var);
874893
break;
875894
}
876895
}
@@ -2586,9 +2605,7 @@ exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt)
25862605
* Store the eventually assigned cursor name in the cursor variable
25872606
* ----------
25882607
*/
2589-
if (curvar->freeval)
2590-
pfree((void *) (curvar->value));
2591-
2608+
free_var(curvar);
25922609
curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
25932610
curvar->isnull = false;
25942611
curvar->freeval = true;
@@ -2684,9 +2701,7 @@ exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt)
26842701
* Store the eventually assigned portal name in the cursor variable
26852702
* ----------
26862703
*/
2687-
if (curvar->freeval)
2688-
pfree((void *) (curvar->value));
2689-
2704+
free_var(curvar);
26902705
curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
26912706
curvar->isnull = false;
26922707
curvar->freeval = true;
@@ -2857,11 +2872,7 @@ exec_assign_value(PLpgSQL_execstate *estate,
28572872
errmsg("NULL cannot be assigned to variable \"%s\" declared NOT NULL",
28582873
var->refname)));
28592874

2860-
if (var->freeval)
2861-
{
2862-
pfree(DatumGetPointer(var->value));
2863-
var->freeval = false;
2864-
}
2875+
free_var(var);
28652876

28662877
/*
28672878
* If type is by-reference, make sure we have a freshly
@@ -4343,3 +4354,13 @@ plpgsql_xact_cb(XactEvent event, void *arg)
43434354
FreeExecutorState(simple_eval_estate);
43444355
simple_eval_estate = NULL;
43454356
}
4357+
4358+
static void
4359+
free_var(PLpgSQL_var *var)
4360+
{
4361+
if (var->freeval)
4362+
{
4363+
pfree(DatumGetPointer(var->value));
4364+
var->freeval = false;
4365+
}
4366+
}

‎src/pl/plpgsql/src/pl_funcs.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* procedural language
44
*
55
* IDENTIFICATION
6-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.40 2005/04/05 06:22:16 tgl Exp $
6+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.41 2005/06/10 16:23:11 neilc Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -634,7 +634,7 @@ dump_block(PLpgSQL_stmt_block *block)
634634
{
635635
ListCell *e;
636636

637-
foreach (e, block->exceptions)
637+
foreach (e, block->exceptions->exc_list)
638638
{
639639
PLpgSQL_exception *exc = (PLpgSQL_exception *) lfirst(e);
640640
PLpgSQL_condition *cond;

‎src/pl/plpgsql/src/plpgsql.h

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* procedural language
44
*
55
* IDENTIFICATION
6-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.61 2005/06/07 02:47:18 neilc Exp $
6+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.62 2005/06/10 16:23:11 neilc Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -322,6 +322,13 @@ typedef struct PLpgSQL_condition
322322
struct PLpgSQL_condition *next;
323323
} PLpgSQL_condition;
324324

325+
typedef struct
326+
{
327+
int sqlstate_varno;
328+
int sqlerrm_varno;
329+
List *exc_list; /* List of WHEN clauses */
330+
} PLpgSQL_exception_block;
331+
325332
typedef struct
326333
{ /* One EXCEPTION ... WHEN clause */
327334
int lineno;
@@ -336,9 +343,9 @@ typedef struct
336343
int lineno;
337344
char *label;
338345
List *body; /* List of statements */
339-
List *exceptions; /* List of WHEN clauses */
340346
int n_initvars;
341347
int *initvarnos;
348+
PLpgSQL_exception_block *exceptions;
342349
} PLpgSQL_stmt_block;
343350

344351

0 commit comments

Comments
 (0)