From beda4814c12d8dc8dc455cb96f0b1055fb149ecb Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 31 Jul 2004 07:39:21 +0000 Subject: plpgsql does exceptions. There are still some things that need refinement; in particular I fear that the recognized set of error condition names probably has little in common with what Oracle recognizes. But it's a start. --- doc/src/sgml/plpgsql.sgml | 130 ++++++++++++++++++++++++++++++++++++---------- 1 file changed, 104 insertions(+), 26 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index b30cc7e83fe..7f3f84448d7 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,5 +1,5 @@ @@ -1796,6 +1796,101 @@ END LOOP; rather than the simple syntax error one might expect to get. + + + + Trapping Errors + + + 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>> + DECLARE + declarations +BEGIN + statements +EXCEPTION + WHEN condition THEN + handler_statements + WHEN condition THEN + handler_statements + ... + +END; + + + + + If no error occurs, this form of block simply executes all the + statements, and then control passes + to the next statement after END. But if an error + occurs within the statements, further + processing of the statements is + abandoned, and control passes to the EXCEPTION list. + The list is searched for the first condition + matching the error that occurred. If a match is found, the + corresponding handler_statements are + executed, and then control passes to the next statement after + END. If no match is found, the error propagates out + as though the EXCEPTION clause were not there at all: + the error can be caught by an enclosing block with + EXCEPTION, or if there is none it aborts processing + of the function. The special condition name OTHERS + matches every error type except QUERY_CANCELED. + (It is possible, but usually not a good idea, to trap + QUERY_CANCELED by name.) + + + + If a new error occurs within the selected + handler_statements, it cannot be caught + by this EXCEPTION clause, but is propagated out. + A surrounding EXCEPTION clause could catch it. + + + + When an error is caught by an EXCEPTION clause, + the local variables of the PL/pgSQL function + remain as they were when the error occurred, but all changes + to persistent database state within the block are rolled back. + As an example, consider this fragment: + + + INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones'); + BEGIN + UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones'; + x := x + 1; + y := x / 0; + EXCEPTION + WHEN division_by_zero THEN + RAISE NOTICE 'caught division_by_zero'; + RETURN x; + END; + + + When control reaches the assignment to y, it will + fail with a division_by_zero error. This will be caught by + the EXCEPTION clause. The value returned in the + RETURN statement will be the incremented value of + x, but the effects of the UPDATE command will + have been rolled back. The INSERT command is not rolled + back, however, so the end result is that the database contains + Tom Jones not Joe Jones. + + + + + A block containing an EXCEPTION clause is significantly + more expensive to enter and exit than a block without one. Therefore, + don't use EXCEPTION without need. + + @@ -2120,11 +2215,11 @@ RAISE level '