Skip site navigation (1)
Skip section navigation (2)
Peripheral Links
Header And Logo
|
Site Navigation
User's exception plpgsql
- From: Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz>
- To: pgsql-patches(at)postgresql(dot)org
- Subject: User's exception plpgsql
- Date: Sun, 26 Jun 2005 09:21:44 +0200 (CEST)
- Message-id: <Pine.LNX.4.44.0506260904040.21791-200000@kix.fsv.cvut.cz> <text/plain>
Hello
Per small recent discussion I corrected patch user's exception.
diff: User can choise any sqlstate (without class U0, which I reserve as
range for default values sqlstates - if user don't spec sqlstate, is
used value from this range). There is only basic changes in documentation
and needs enhancing. I am not able to do (I am sorry, my english is poor).
Note: patch don't create deep changes in plpgsql core. Only enhance stmts
DECLARE, RAISE and EXCEPTION condition.
Next ToDo (needs discussion):
+ Optional message in raise stmt for user's or system exception
raise exception division_by_zero;
+ Possibility rethrown exception
raise;
Regards
Pavel Stehule
diff -c -r --new-file pgsql.01/doc/src/sgml/plpgsql.sgml pgsql.02/doc/src/sgml/plpgsql.sgml
*** pgsql.01/doc/src/sgml/plpgsql.sgml 2005-06-25 15:29:27.000000000 +0200
--- pgsql.02/doc/src/sgml/plpgsql.sgml 2005-06-25 21:56:24.000000000 +0200
***************
*** 2116,2122 ****
<para>
The <replaceable>condition</replaceable> names can be any of those
shown in <xref linkend="errcodes-appendix">. A category name matches
! any error within its category.
The special condition name <literal>OTHERS</>
matches every error type except <literal>QUERY_CANCELED</>.
(It is possible, but often unwise, to trap
--- 2116,2124 ----
<para>
The <replaceable>condition</replaceable> names can be any of those
shown in <xref linkend="errcodes-appendix">. A category name matches
! any error within its category. You can use exception variable as
! condition name. Exception variable is declared with type
! <literal>EXCEPTION</literal>
The special condition name <literal>OTHERS</>
matches every error type except <literal>QUERY_CANCELED</>.
(It is possible, but often unwise, to trap
***************
*** 2570,2576 ****
raise errors.
<synopsis>
! RAISE <replaceable class="parameter">level</replaceable> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ...</optional></optional>;
</synopsis>
Possible levels are <literal>DEBUG</literal>,
--- 2572,2579 ----
raise errors.
<synopsis>
! RAISE <replaceable class="parameter">level</replaceable>
! <optional>system exception|exception variable</optional> '<replaceable class="parameter">format</replaceable>' <optional>, <replaceable class="parameter">expression</replaceable> <optional>, ...</optional></optional>;
</synopsis>
Possible levels are <literal>DEBUG</literal>,
***************
*** 2587,2592 ****
--- 2590,2599 ----
variables. See <xref linkend="runtime-config"> for more
information.
</para>
+
+ <para>
+ You can specify any system exception or any user exception.
+ </para>
<para>
Inside the format string, <literal>%</literal> is replaced by the
diff -c -r --new-file pgsql.01/src/pl/plpgsql/src/gram.y pgsql.02/src/pl/plpgsql/src/gram.y
*** pgsql.01/src/pl/plpgsql/src/gram.y 2005-06-25 15:21:22.000000000 +0200
--- pgsql.02/src/pl/plpgsql/src/gram.y 2005-06-25 19:57:42.000000000 +0200
***************
*** 39,44 ****
--- 39,45 ----
#include "plpgsql.h"
#include "parser/parser.h"
+ #include "utils/elog.h"
static PLpgSQL_expr *read_sql_construct(int until,
int until2,
***************
*** 78,83 ****
--- 79,90 ----
} forvariable;
struct
{
+ bool nospec;
+ int sqlstate;
+ char *refname;
+ } opt_excptspec;
+ struct
+ {
char *label;
int n_initvars;
int *initvarnos;
***************
*** 103,108 ****
--- 110,116 ----
PLpgSQL_exception_block *exception_block;
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
+ PLpgSQL_usrexcpt *usrexcpt;
}
%type <declhdr> decl_sect
***************
*** 115,125 ****
--- 123,135 ----
%type <list> decl_cursor_arglist
%type <nsitem> decl_aliasitem
%type <str> decl_stmts decl_stmt
+ %type <str> decl_defsqlstate
%type <expr> expr_until_semi expr_until_rightbracket
%type <expr> expr_until_then expr_until_loop
%type <expr> opt_exitcond
+
%type <ival> assign_var cursor_variable
%type <var> cursor_varptr
%type <variable> decl_cursor_arg
***************
*** 144,150 ****
%type <exception_block> exception_sect
%type <exception> proc_exception
%type <condition> proc_conditions
!
%type <ival> raise_level
%type <str> raise_msg
--- 154,161 ----
%type <exception_block> exception_sect
%type <exception> proc_exception
%type <condition> proc_conditions
! %type <opt_excptspec> opt_excptspec
! %type <str> excpt_name
%type <ival> raise_level
%type <str> raise_msg
***************
*** 223,228 ****
--- 234,240 ----
%token T_LABEL
%token T_WORD
%token T_ERROR
+ %token T_EXCEPTION
%token O_OPTION
%token O_DUMP
***************
*** 332,338 ****
PLpgSQL_variable *var;
var = plpgsql_build_variable($1.name, $1.lineno,
! $3, true);
if ($2)
{
if (var->dtype == PLPGSQL_DTYPE_VAR)
--- 344,351 ----
PLpgSQL_variable *var;
var = plpgsql_build_variable($1.name, $1.lineno,
!
! $3, true);
if ($2)
{
if (var->dtype == PLPGSQL_DTYPE_VAR)
***************
*** 361,366 ****
--- 374,408 ----
errmsg("default value for row or record variable is not supported")));
}
}
+ | decl_varname K_EXCEPTION decl_defsqlstate
+ {
+ PLpgSQL_usrexcpt *ue;
+ PLpgSQL_type *dtype;
+
+ dtype = (PLpgSQL_type *) palloc(sizeof(PLpgSQL_type));
+ dtype->typname = "exception";
+ dtype->ttype = PLPGSQL_TTYPE_EXCEPTION;
+
+
+ ue = (PLpgSQL_usrexcpt *) plpgsql_build_variable($1.name, $1.lineno,
+ dtype, true);
+ if ($3)
+ {
+ if (strlen($3) != 5)
+ yyerror("Sqlstate has five chars");
+
+ if (strncmp($3,"U0",2) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("Invalid class for SQLSTATE value '%s' for user's exception.", $3),
+ errhint("Class 'U0' is reserved for default values user's exceptions.")));
+
+ ue->sqlstate = MAKE_SQLSTATE($3[0],$3[1],$3[2],$3[3],$3[4]);
+ } else
+ ue->sqlstate = plpgsql_newUsrExceptions();
+
+ pfree(dtype);
+ }
| decl_varname K_ALIAS K_FOR decl_aliasitem ';'
{
plpgsql_ns_additem($4->itemtype,
***************
*** 563,568 ****
--- 605,626 ----
| K_DEFAULT
;
+ decl_defsqlstate : ';'
+ { $$ = NULL; }
+ | decl_defkey
+ {
+ if (yylex() != T_STRING)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("Default value for exception type have to be string constant")));
+ $$ = plpgsql_get_string_value();
+ if (yylex() != ';')
+ yyerror("syntax error");
+
+ }
+ ;
+
+
proc_sect :
{
$$ = NIL;
***************
*** 1185,1191 ****
}
;
! stmt_raise : K_RAISE lno raise_level raise_msg
{
PLpgSQL_stmt_raise *new;
int tok;
--- 1243,1249 ----
}
;
! stmt_raise : K_RAISE lno raise_level opt_excptspec raise_msg
{
PLpgSQL_stmt_raise *new;
int tok;
***************
*** 1195,1203 ****
new->cmd_type = PLPGSQL_STMT_RAISE;
new->lineno = $2;
new->elog_level = $3;
! new->message = $4;
new->params = NIL;
tok = yylex();
/*
--- 1253,1273 ----
new->cmd_type = PLPGSQL_STMT_RAISE;
new->lineno = $2;
new->elog_level = $3;
! new->message = $5;
new->params = NIL;
+ if ($4.nospec == false)
+ {
+ new->sqlstate = $4.sqlstate;
+ new->refname = $4.refname;
+ }
+ else
+ {
+ new->sqlstate = (new->elog_level >= ERROR)?ERRCODE_RAISE_EXCEPTION:0;
+ new->refname = NULL;
+ }
+
+
tok = yylex();
/*
***************
*** 1260,1265 ****
--- 1330,1358 ----
}
;
+ opt_excptspec : T_EXCEPTION
+ {
+ $$.nospec = false;
+ $$.sqlstate = yylval.usrexcpt->sqlstate;
+ $$.refname = yylval.usrexcpt->refname;
+ }
+ | T_WORD
+ {
+
+ PLpgSQL_condition *c = plpgsql_parse_err_condition(yytext);
+ if (c->sqlerrstate == 0) /* others */
+ yyerror("You have to use exception's variable or system exception");
+
+ $$.nospec = false;
+ $$.sqlstate = c->sqlerrstate;
+ $$.refname = c->condname;
+ }
+ | /* EMPTY */
+ {
+ $$.nospec = true;
+ }
+
+
loop_body : proc_sect lno K_END K_LOOP opt_endlabel ';'
{
$$.list = $1;
***************
*** 1583,1589 ****
}
;
! proc_conditions : proc_conditions K_OR opt_lblname
{
PLpgSQL_condition *old;
--- 1676,1682 ----
}
;
! proc_conditions : proc_conditions K_OR excpt_name
{
PLpgSQL_condition *old;
***************
*** 1593,1604 ****
$$ = $1;
}
! | opt_lblname
{
$$ = plpgsql_parse_err_condition($1);
}
;
expr_until_semi :
{ $$ = plpgsql_read_expression(';', ";"); }
;
--- 1686,1711 ----
$$ = $1;
}
! | excpt_name
{
$$ = plpgsql_parse_err_condition($1);
}
;
+ excpt_name: T_WORD
+ {
+ char *name;
+
+ plpgsql_convert_ident(yytext, &name, 1);
+ $$ = name;
+ }
+ | T_EXCEPTION
+ {
+ $$ = yylval.usrexcpt->refname;
+ }
+ ;
+
+
expr_until_semi :
{ $$ = plpgsql_read_expression(';', ";"); }
;
diff -c -r --new-file pgsql.01/src/pl/plpgsql/src/pl_comp.c pgsql.02/src/pl/plpgsql/src/pl_comp.c
*** pgsql.01/src/pl/plpgsql/src/pl_comp.c 2005-06-24 13:11:25.000000000 +0200
--- pgsql.02/src/pl/plpgsql/src/pl_comp.c 2005-06-25 20:03:14.000000000 +0200
***************
*** 80,85 ****
--- 80,87 ----
bool plpgsql_DumpExecTree = false;
bool plpgsql_check_syntax = false;
+ int plpgsql_user_excpt;
+
PLpgSQL_function *plpgsql_curr_compile;
/* A context appropriate for short-term allocs during compilation */
***************
*** 315,320 ****
--- 317,324 ----
/* This is short-lived, so needn't allocate in function's cxt */
plpgsql_Datums = palloc(sizeof(PLpgSQL_datum *) * datums_alloc);
datums_last = 0;
+
+ plpgsql_user_excpt = 0;
/*
* Do extra syntax checks when validating the function
***************
*** 904,910 ****
case PLPGSQL_NSTYPE_ROW:
plpgsql_yylval.row = (PLpgSQL_row *) (plpgsql_Datums[nse->itemno]);
return T_ROW;
!
default:
return T_ERROR;
}
--- 908,918 ----
case PLPGSQL_NSTYPE_ROW:
plpgsql_yylval.row = (PLpgSQL_row *) (plpgsql_Datums[nse->itemno]);
return T_ROW;
!
! case PLPGSQL_NSTYPE_EXCEPTION:
! plpgsql_yylval.usrexcpt = (PLpgSQL_usrexcpt *) (plpgsql_Datums[nse->itemno]);
! return T_EXCEPTION;
!
default:
return T_ERROR;
}
***************
*** 1626,1631 ****
--- 1634,1658 ----
result = (PLpgSQL_variable *) rec;
break;
}
+ case PLPGSQL_TTYPE_EXCEPTION:
+ {
+ /* Exception pseudo type */
+ PLpgSQL_usrexcpt *excpt;
+
+ excpt = palloc0(sizeof(PLpgSQL_usrexcpt));
+ excpt->dtype = PLPGSQL_DTYPE_EXCEPTION;
+ excpt->refname = pstrdup(refname);
+ excpt->lineno = lineno;
+
+ plpgsql_adddatum((PLpgSQL_datum *) excpt);
+ if (add2namespace)
+ plpgsql_ns_additem(PLPGSQL_NSTYPE_EXCEPTION,
+ excpt->eno,
+ refname);
+ result = (PLpgSQL_variable *) excpt;
+ break;
+ }
+
case PLPGSQL_TTYPE_PSEUDO:
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
***************
*** 1893,1898 ****
--- 1920,1928 ----
PLpgSQL_condition *new;
PLpgSQL_condition *prev;
+ PLpgSQL_nsitem *nse;
+ char *cp[1];
+
/*
* XXX Eventually we will want to look for user-defined exception
* names here.
***************
*** 1924,1929 ****
--- 1954,1986 ----
}
}
+ if (!prev)
+ {
+ /* Do case conversion and word separation */
+ plpgsql_convert_ident(condname, cp, 1);
+
+ /*
+ * Do a lookup on the compiler's namestack
+ */
+ nse = plpgsql_ns_lookup(cp[0], NULL);
+
+ if (nse != NULL)
+ {
+ PLpgSQL_usrexcpt *excpt = (PLpgSQL_usrexcpt *) (plpgsql_Datums[nse->itemno]);
+ if (nse->itemtype == PLPGSQL_NSTYPE_EXCEPTION)
+ {
+ new = palloc(sizeof(PLpgSQL_condition));
+ new->sqlerrstate = excpt->sqlstate;
+ new->condname = condname;
+ new->next = prev;
+ prev = new;
+ }
+ }
+ pfree(cp[0]);
+ }
+
+
+
if (!prev)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
***************
*** 2177,2179 ****
--- 2234,2251 ----
if (hentry == NULL)
elog(WARNING, "trying to delete function that does not exist");
}
+
+ #define MAX_USER_EXCPT 999
+
+ int
+ plpgsql_newUsrExceptions(void)
+ {
+ char rs[4];
+
+ if (plpgsql_user_excpt == MAX_USER_EXCPT)
+ ereport(ERROR,
+ (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+ errmsg("Too much user's exception")));
+ sprintf(rs,"%03d", ++plpgsql_user_excpt);
+ return MAKE_SQLSTATE('U','0', rs[0],rs[1],rs[2]);
+ }
diff -c -r --new-file pgsql.01/src/pl/plpgsql/src/pl_exec.c pgsql.02/src/pl/plpgsql/src/pl_exec.c
*** pgsql.01/src/pl/plpgsql/src/pl_exec.c 2005-06-24 13:11:25.000000000 +0200
--- pgsql.02/src/pl/plpgsql/src/pl_exec.c 2005-06-25 19:36:02.000000000 +0200
***************
*** 722,727 ****
--- 722,730 ----
*/
result = datum;
break;
+ case PLPGSQL_DTYPE_EXCEPTION:
+ result = NULL;
+ break;
default:
elog(ERROR, "unrecognized dtype: %d", datum->dtype);
***************
*** 825,830 ****
--- 828,834 ----
case PLPGSQL_DTYPE_RECFIELD:
case PLPGSQL_DTYPE_ARRAYELEM:
+ case PLPGSQL_DTYPE_EXCEPTION:
break;
default:
***************
*** 2061,2069 ****
*/
estate->err_text = raise_skip_msg; /* suppress traceback of raise */
! ereport(stmt->elog_level,
! ((stmt->elog_level >= ERROR) ? errcode(ERRCODE_RAISE_EXCEPTION) : 0,
! errmsg_internal("%s", plpgsql_dstring_get(&ds))));
estate->err_text = NULL; /* un-suppress... */
--- 2065,2081 ----
*/
estate->err_text = raise_skip_msg; /* suppress traceback of raise */
!
! if (stmt->refname != NULL)
! ereport(stmt->elog_level, /* User's exception */
! (errcode(stmt->sqlstate),
! errdetail("User's exception/notice - sqlstate: '%s', name: '%s'", unpack_sql_state(stmt->sqlstate), stmt->refname),
! errhint("from RAISE stmt on line %d", stmt->lineno),
! errmsg_internal("%s", plpgsql_dstring_get(&ds))));
! else
! ereport(stmt->elog_level,
! (errcode(stmt->sqlstate),
! errmsg_internal("%s", plpgsql_dstring_get(&ds))));
estate->err_text = NULL; /* un-suppress... */
diff -c -r --new-file pgsql.01/src/pl/plpgsql/src/plpgsql.h pgsql.02/src/pl/plpgsql/src/plpgsql.h
*** pgsql.01/src/pl/plpgsql/src/plpgsql.h 2005-06-24 13:11:25.000000000 +0200
--- pgsql.02/src/pl/plpgsql/src/plpgsql.h 2005-06-25 19:27:28.000000000 +0200
***************
*** 58,64 ****
PLPGSQL_NSTYPE_LABEL,
PLPGSQL_NSTYPE_VAR,
PLPGSQL_NSTYPE_ROW,
! PLPGSQL_NSTYPE_REC
};
/* ----------
--- 58,65 ----
PLPGSQL_NSTYPE_LABEL,
PLPGSQL_NSTYPE_VAR,
PLPGSQL_NSTYPE_ROW,
! PLPGSQL_NSTYPE_REC,
! PLPGSQL_NSTYPE_EXCEPTION
};
/* ----------
***************
*** 73,79 ****
PLPGSQL_DTYPE_RECFIELD,
PLPGSQL_DTYPE_ARRAYELEM,
PLPGSQL_DTYPE_EXPR,
! PLPGSQL_DTYPE_TRIGARG
};
/* ----------
--- 74,81 ----
PLPGSQL_DTYPE_RECFIELD,
PLPGSQL_DTYPE_ARRAYELEM,
PLPGSQL_DTYPE_EXPR,
! PLPGSQL_DTYPE_TRIGARG,
! PLPGSQL_DTYPE_EXCEPTION
};
/* ----------
***************
*** 85,91 ****
PLPGSQL_TTYPE_SCALAR, /* scalar types and domains */
PLPGSQL_TTYPE_ROW, /* composite types */
PLPGSQL_TTYPE_REC, /* RECORD pseudotype */
! PLPGSQL_TTYPE_PSEUDO /* other pseudotypes */
};
/* ----------
--- 87,94 ----
PLPGSQL_TTYPE_SCALAR, /* scalar types and domains */
PLPGSQL_TTYPE_ROW, /* composite types */
PLPGSQL_TTYPE_REC, /* RECORD pseudotype */
! PLPGSQL_TTYPE_PSEUDO, /* other pseudotypes */
! PLPGSQL_TTYPE_EXCEPTION
};
/* ----------
***************
*** 190,195 ****
--- 193,207 ----
int lineno;
} PLpgSQL_variable;
+ typedef struct
+ {
+ int dtype; /* Exception variable */
+ int eno;
+ char *refname;
+ int lineno;
+ int sqlstate;
+ } PLpgSQL_usrexcpt;
+
typedef struct PLpgSQL_expr
{ /* SQL Query to plan and execute */
int dtype;
***************
*** 516,521 ****
--- 528,535 ----
int cmd_type;
int lineno;
int elog_level;
+ int sqlstate;
+ char *refname;
char *message;
List *params; /* list of expressions */
} PLpgSQL_stmt_raise;
***************
*** 688,693 ****
--- 702,709 ----
extern int plpgsql_add_initdatums(int **varnos);
extern void plpgsql_HashTableInit(void);
extern void plpgsql_compile_error_callback(void *arg);
+ extern int plpgsql_newUsrExceptions(void);
+
/* ----------
* Functions in pl_handler.c
diff -c -r --new-file pgsql.01/src/test/regress/expected/plpgsql.out pgsql.02/src/test/regress/expected/plpgsql.out
*** pgsql.01/src/test/regress/expected/plpgsql.out 2005-06-25 15:23:17.000000000 +0200
--- pgsql.02/src/test/regress/expected/plpgsql.out 2005-06-25 20:34:05.000000000 +0200
***************
*** 2709,2711 ****
--- 2709,2770 ----
DROP FUNCTION
drop function vfoo2();
ERROR: function vfoo2() does not exist
+
+ -- user's exception
+ create function innerfx() returns integer as $$
+ declare my_excpt exception = 'U0001';
+ begin -- using msgtext as one param of exception
+ raise exception my_excpt '%', CURRENT_TIMESTAMP;
+ return 1;
+ end $$ language plpgsql;
+ ERROR: Invalid class for SQLSTATE value 'U0001' for user's exception.
+ HINT: Class 'U0' is reserved for default values user's exceptions.
+ CONTEXT: compile of PL/pgSQL function "innerfx" near line 1
+ create function innerfx() returns integer as $$
+ declare my_excpt exception = 'U1001';
+ begin -- using msgtext as one param of exception
+ raise exception my_excpt '%', 100.34::numeric;
+ return 1;
+ end $$ language plpgsql;
+ CREATE FUNCTION
+ create function outerfx() returns integer as $$
+ declare
+ my_excpt exception = 'U1001';
+ alias_div_by_zero exception = '22012';
+ my_excpt_def_sqlstate exception;
+ begin
+ begin
+ raise exception my_excpt_def_sqlstate 'foo';
+ exception when my_excpt_def_sqlstate then
+ raise notice '01 catch: %, %', sqlstate, sqlerrm;
+ end;
+ begin
+ raise notice '%', innerfx();
+ exception when my_excpt then
+ raise notice '02 catch: %, %', sqlstate, sqlerrm::numeric;
+ end;
+ begin
+ raise exception alias_div_by_zero 'testing';
+ exception when division_by_zero then
+ raise notice 'Divison by zero: %, %', sqlstate, sqlerrm;
+ end;
+ return 1;
+ end; $$ language plpgsql;
+ CREATE FUNCTION
+ select innerfx();
+ ERROR: 100.34
+ DETAIL: User's exception/notice - sqlstate: 'U1001', name: 'my_excpt'
+ HINT: from RAISE stmt on line 3
+ select outerfx();
+ NOTICE: 01 catch: U0001, foo
+ NOTICE: 02 catch: U1001, 100.34
+ NOTICE: Divison by zero: 22012, testing
+ outerfx
+ ---------
+ 1
+ (1 row)
+
+ drop function outerfx();
+ DROP FUNCTION
+ drop function innerfx();
+ DROP FUNCTION
diff -c -r --new-file pgsql.01/src/test/regress/sql/plpgsql.sql pgsql.02/src/test/regress/sql/plpgsql.sql
*** pgsql.01/src/test/regress/sql/plpgsql.sql 2005-06-25 14:10:30.000000000 +0200
--- pgsql.02/src/test/regress/sql/plpgsql.sql 2005-06-25 20:32:59.000000000 +0200
***************
*** 2266,2268 ****
--- 2266,2314 ----
drop function vfoo();
drop function vfoo2();
+
+ -- user's exception
+ create function innerfx() returns integer as $$
+ declare my_excpt exception = 'U0001';
+ begin -- using msgtext as one param of exception
+ raise exception my_excpt '%', CURRENT_TIMESTAMP;
+ return 1;
+ end $$ language plpgsql;
+
+
+ create function innerfx() returns integer as $$
+ declare my_excpt exception = 'U1001';
+ begin -- using msgtext as one param of exception
+ raise exception my_excpt '%', 100.34::numeric;
+ return 1;
+ end $$ language plpgsql;
+
+ create function outerfx() returns integer as $$
+ declare
+ my_excpt exception = 'U1001';
+ alias_div_by_zero exception = '22012';
+ my_excpt_def_sqlstate exception;
+ begin
+ begin
+ raise exception my_excpt_def_sqlstate 'foo';
+ exception when my_excpt_def_sqlstate then
+ raise notice '01 catch: %, %', sqlstate, sqlerrm;
+ end;
+ begin
+ raise notice '%', innerfx();
+ exception when my_excpt then
+ raise notice '02 catch: %, %', sqlstate, sqlerrm::numeric;
+ end;
+ begin
+ raise exception alias_div_by_zero 'testing';
+ exception when division_by_zero then
+ raise notice 'Divison by zero: %, %', sqlstate, sqlerrm;
+ end;
+ return 1;
+ end; $$ language plpgsql;
+
+ select innerfx();
+ select outerfx();
+
+ drop function outerfx();
+ drop function innerfx();
- References:
- pl/pgsql: END verbosity [patch]
- From: Pavel Stehule
- pl/pgsql: END verbosity [patch]
- Prev by Date: pl/pgsql: END verbosity [patch]
- Next by Date: ToDo: Allow PL/pgSQL EXECUTE query_var INTO record_var;
- Previous by thread: pl/pgsql: END verbosity [patch]
- Next by thread: Re: [HACKERS] language handlers in public schema?
- Indexes: