Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit38af680

Browse files
committed
Add PL/pgSQL SQLSTATE and SQLERRM support which sets these values on
error.Pavel Stehule
1 parent8c792fe commit38af680

File tree

6 files changed

+133
-17
lines changed

6 files changed

+133
-17
lines changed

‎doc/src/sgml/plpgsql.sgml‎

Lines changed: 8 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.67 2005/04/19 03:55:43 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.68 2005/05/26 00:16:31 momjian Exp $
33
-->
44

55
<chapter id="plpgsql">
@@ -2007,12 +2007,13 @@ END LOOP;
20072007
</indexterm>
20082008

20092009
<para>
2010-
By default, any error occurring in a <application>PL/pgSQL</>
2011-
function aborts execution of the function, and indeed of the
2012-
surrounding transaction as well. You can trap errors and recover
2013-
from them by using a <command>BEGIN</> block with an
2014-
<literal>EXCEPTION</> clause. The syntax is an extension of the
2015-
normal syntax for a <command>BEGIN</> block:
2010+
Any error occurring in <application>PL/pgSQL</> sets variables
2011+
<varname>SQLSTATE</> and <varname>SQLERRM</>, and, by default,
2012+
aborts execution of the function, and indeed of the surrounding
2013+
transaction as well. You can trap errors and recover from them by
2014+
using a <command>BEGIN</> block with an <literal>EXCEPTION</>
2015+
clause. The syntax is an extension of the normal syntax for a
2016+
<command>BEGIN</> block:
20162017

20172018
<synopsis>
20182019
<optional> &lt;&lt;<replaceable>label</replaceable>&gt;&gt; </optional>

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

Lines changed: 27 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.69 2005/04/07 14:53:04 tgl Exp $
7+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.70 2005/05/26 00:16:31 momjian Exp $
88
*
99
* This software is copyrighted by Jan Wieck - Hamburg.
1010
*
@@ -80,6 +80,11 @@ staticvoid plpgsql_sql_error_callback(void *arg);
8080
int n_initvars;
8181
int *initvarnos;
8282
}declhdr;
83+
struct
84+
{
85+
int sqlstate_varno;
86+
int sqlerrm_varno;
87+
}fict_vars;
8388
List*list;
8489
PLpgSQL_type*dtype;
8590
PLpgSQL_datum*scalar;/* a VAR, RECFIELD, or TRIGARG*/
@@ -96,6 +101,7 @@ staticvoid plpgsql_sql_error_callback(void *arg);
96101
PLpgSQL_diag_item*diagitem;
97102
}
98103

104+
%type<fict_vars>fict_vars_sect
99105
%type<declhdr>decl_sect
100106
%type<varname>decl_varname
101107
%type<str>decl_renname
@@ -244,26 +250,42 @@ opt_semi:
244250
|';'
245251
;
246252

247-
pl_block:decl_sectK_BEGINlnoproc_sectexception_sectK_END
253+
pl_block:decl_sectfict_vars_sectK_BEGINlnoproc_sectexception_sectK_END
248254
{
249255
PLpgSQL_stmt_block *new;
250256

251257
new = palloc0(sizeof(PLpgSQL_stmt_block));
252258

253259
new->cmd_type= PLPGSQL_STMT_BLOCK;
254-
new->lineno=$3;
260+
new->lineno=$4;
255261
new->label=$1.label;
256262
new->n_initvars =$1.n_initvars;
257263
new->initvarnos =$1.initvarnos;
258-
new->body=$4;
259-
new->exceptions=$5;
264+
new->body=$5;
265+
new->exceptions=$6;
266+
267+
new->sqlstate_varno =$2.sqlstate_varno;
268+
new->sqlerrm_varno =$2.sqlerrm_varno;
260269

261270
plpgsql_ns_pop();
262271

263272
$$ = (PLpgSQL_stmt *)new;
264273
}
265274
;
266275

276+
fict_vars_sect:
277+
{
278+
PLpgSQL_variable*var;
279+
280+
plpgsql_ns_setlocal(false);
281+
var = plpgsql_build_variable("sqlstate",0,
282+
plpgsql_build_datatype(TEXTOID, -1), true);
283+
$$.sqlstate_varno = var->dno;
284+
var = plpgsql_build_variable("sqlerrm",0,
285+
plpgsql_build_datatype(TEXTOID, -1), true);
286+
$$.sqlerrm_varno = var->dno;
287+
plpgsql_add_initdatums(NULL);
288+
};
267289

268290
decl_sect:opt_label
269291
{

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

Lines changed: 46 additions & 1 deletion
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.138 2005/05/06 17:24:55 tgl Exp $
6+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.139 2005/05/26 00:16:31 momjian Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -180,6 +180,7 @@ static Datum exec_simple_cast_value(Datum value, Oid valtype,
180180
staticvoidexec_init_tuple_store(PLpgSQL_execstate*estate);
181181
staticboolcompatible_tupdesc(TupleDesctd1,TupleDesctd2);
182182
staticvoidexec_set_found(PLpgSQL_execstate*estate,boolstate);
183+
staticchar*unpack_sql_state(intssval);
183184

184185

185186
/* ----------
@@ -747,6 +748,20 @@ exec_stmt_block(PLpgSQL_execstate *estate, PLpgSQL_stmt_block *block)
747748
inti;
748749
intn;
749750

751+
752+
/* setup SQLSTATE and SQLERRM */
753+
PLpgSQL_var*var;
754+
755+
var= (PLpgSQL_var*) (estate->datums[block->sqlstate_varno]);
756+
var->isnull= false;
757+
var->freeval= true;
758+
var->value=DirectFunctionCall1(textin,CStringGetDatum("00000"));
759+
760+
var= (PLpgSQL_var*) (estate->datums[block->sqlerrm_varno]);
761+
var->isnull= false;
762+
var->freeval= true;
763+
var->value=DirectFunctionCall1(textin,CStringGetDatum("Sucessful completion"));
764+
750765
/*
751766
* First initialize all variables declared in this block
752767
*/
@@ -855,6 +870,16 @@ exec_stmt_block(PLpgSQL_execstate *estate, PLpgSQL_stmt_block *block)
855870
RollbackAndReleaseCurrentSubTransaction();
856871
MemoryContextSwitchTo(oldcontext);
857872
CurrentResourceOwner=oldowner;
873+
874+
/* set SQLSTATE and SQLERRM variables */
875+
876+
var= (PLpgSQL_var*) (estate->datums[block->sqlstate_varno]);
877+
pfree((void*) (var->value));
878+
var->value=DirectFunctionCall1(textin,CStringGetDatum(unpack_sql_state(edata->sqlerrcode)));
879+
880+
var= (PLpgSQL_var*) (estate->datums[block->sqlerrm_varno]);
881+
pfree((void*) (var->value));
882+
var->value=DirectFunctionCall1(textin,CStringGetDatum(edata->message));
858883

859884
/*
860885
* If AtEOSubXact_SPI() popped any SPI context of the subxact,
@@ -919,6 +944,26 @@ exec_stmt_block(PLpgSQL_execstate *estate, PLpgSQL_stmt_block *block)
919944
returnPLPGSQL_RC_OK;
920945
}
921946

947+
/*
948+
* unpack MAKE_SQLSTATE code
949+
* This code is copied from backend/utils/error/elog.c.
950+
*/
951+
staticchar*
952+
unpack_sql_state(intssval)
953+
{
954+
staticchartbuf[12];
955+
inti;
956+
957+
for (i=0;i<5;i++)
958+
{
959+
tbuf[i]=PGUNSIXBIT(ssval);
960+
ssval >>=6;
961+
}
962+
tbuf[i]='\0';
963+
returntbuf;
964+
}
965+
966+
922967

923968
/* ----------
924969
* exec_stmtsIterate over a list of statements

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

Lines changed: 6 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/plpgsql.h,v 1.58 2005/04/05 06:22:16 tgl Exp $
6+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.59 2005/05/26 00:16:31 momjian Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -336,9 +336,11 @@ typedef struct
336336
intlineno;
337337
char*label;
338338
List*body;/* List of statements */
339-
List*exceptions;/* List of WHEN clauses */
340-
intn_initvars;
341-
int*initvarnos;
339+
List*exceptions;/* List of WHEN clauses */
340+
intn_initvars;
341+
int*initvarnos;
342+
intsqlstate_varno;
343+
intsqlerrm_varno;
342344
}PLpgSQL_stmt_block;
343345

344346

‎src/test/regress/expected/plpgsql.out‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2380,3 +2380,29 @@ ERROR: control reached end of function without RETURN
23802380
CONTEXT: PL/pgSQL function "missing_return_expr"
23812381
drop function void_return_expr();
23822382
drop function missing_return_expr();
2383+
-- test SQLSTATE and SQLERRM
2384+
create or replace function trap_exceptions() returns void as $_$
2385+
begin
2386+
begin
2387+
raise exception 'first exception';
2388+
exception when others then
2389+
raise notice '% %', SQLSTATE, SQLERRM;
2390+
end;
2391+
raise notice '% %', SQLSTATE, SQLERRM;
2392+
begin
2393+
raise exception 'last exception';
2394+
exception when others then
2395+
raise notice '% %', SQLSTATE, SQLERRM;
2396+
end;
2397+
return;
2398+
end; $_$ language plpgsql;
2399+
select trap_exceptions();
2400+
NOTICE: P0001 first exception
2401+
NOTICE: 00000 Sucessful completion
2402+
NOTICE: P0001 last exception
2403+
trap_exceptions
2404+
-----------------
2405+
2406+
(1 row)
2407+
2408+
drop function trap_exceptions();

‎src/test/regress/sql/plpgsql.sql‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2018,3 +2018,23 @@ select missing_return_expr();
20182018

20192019
dropfunction void_return_expr();
20202020
dropfunction missing_return_expr();
2021+
-- test SQLSTATE and SQLERRM
2022+
create or replacefunctiontrap_exceptions() returns voidas $_$
2023+
begin
2024+
begin
2025+
raise exception'first exception';
2026+
exception when others then
2027+
raise notice'% %', SQLSTATE, SQLERRM;
2028+
end;
2029+
raise notice'% %', SQLSTATE, SQLERRM;
2030+
begin
2031+
raise exception'last exception';
2032+
exception when others then
2033+
raise notice'% %', SQLSTATE, SQLERRM;
2034+
end;
2035+
return;
2036+
end; $_$ language plpgsql;
2037+
2038+
select trap_exceptions();
2039+
2040+
dropfunction trap_exceptions();

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp