From 4107478d374fae8299b6eb17fd13e65b7d8e026a Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 13 May 2008 22:10:30 +0000 Subject: Improve plpgsql's RAISE command. It is now possible to attach DETAIL and HINT fields to a user-thrown error message, and to specify the SQLSTATE error code to use. The syntax has also been tweaked so that the Oracle-compatible case "RAISE exception_name" works (though you won't get a very nice error message if you just write that much). Lastly, support the Oracle-compatible syntax "RAISE" with no parameters to re-throw the current error from within an EXCEPTION block. In passing, allow the syntax SQLSTATE 'nnnnn' within EXCEPTION lists, so that there is a way to trap errors with custom SQLSTATE codes. Pavel Stehule and Tom Lane --- doc/src/sgml/plpgsql.sgml | 126 ++++++++++++++++++++++++++++++++++++++-------- 1 file changed, 104 insertions(+), 22 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 1065eddc74e..09ad6944dba 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,4 +1,4 @@ - + <application>PL/pgSQL</application> - <acronym>SQL</acronym> Procedural Language @@ -2133,7 +2133,12 @@ END; condition name OTHERS matches every error type except QUERY_CANCELED. (It is possible, but often unwise, to trap QUERY_CANCELED by name.) Condition names are - not case-sensitive. + not case-sensitive. Also, an error condition can be specified + by SQLSTATE code; for example these are equivalent: + + WHEN division_by_zero THEN ... + WHEN SQLSTATE '22012' THEN ... + @@ -2750,13 +2755,19 @@ END LOOP label ; raise errors. -RAISE level 'format' , expression , ...; +RAISE level 'format' , expression , ... USING option = expression , ... ; +RAISE level condition_name USING option = expression , ... ; +RAISE level SQLSTATE 'sqlstate' USING option = expression , ... ; +RAISE level USING option = expression , ... ; +RAISE ; - Possible levels are DEBUG, + The level option specifies + the error severity. Allowed levels are DEBUG, LOG, INFO, NOTICE, WARNING, - and EXCEPTION. + and EXCEPTION, with EXCEPTION + being the default. EXCEPTION raises an error (which normally aborts the current transaction); the other levels only generate messages of different priority levels. @@ -2769,19 +2780,17 @@ RAISE level 'level if any, + you can write a format + (which must be a simple string literal, not an expression). The + format string specifies the error message text to be reported. + The format string can be followed + by optional argument expressions to be inserted into the message. Inside the format string, % is replaced by the - next optional argument's string representation. Write + string representation of the next optional argument's value. Write %% to emit a literal %. - Arguments can be simple variables or expressions, - but the format must be a simple string literal. - - In this example, the value of v_job_id will replace the % in the string: @@ -2791,19 +2800,90 @@ RAISE NOTICE 'Calling cs_create_job(%)', v_job_id; - This example will abort the transaction with the given error message: + You can attach additional information to the error report by writing + USING followed by option = expression items. The allowed + option keywords are + MESSAGE, DETAIL, HINT, and + ERRCODE, while each expression can be any string-valued + expression. + MESSAGE sets the error message text (this option can't + be used in the form of RAISE that includes a format + string before USING). + DETAIL supplies an error detail message, while + HINT supplies a hint message. + ERRCODE specifies the error code (SQLSTATE) to report, + either by condition name as shown in , + or directly as a five-character SQLSTATE code. + + + + This example will abort the transaction with the given error message + and hint: + +RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING HINT = 'Please check your user id'; + + + + + These two examples show equivalent ways of setting the SQLSTATE: + +RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation'; +RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505'; + + + + + There is a second RAISE syntax in which the main argument + is the condition name or SQLSTATE to be reported, for example: + +RAISE division_by_zero; +RAISE SQLSTATE '22012'; + + In this syntax, USING can be used to supply a custom + error message, detail, or hint. Another way to do the earlier + example is -RAISE EXCEPTION 'Nonexistent ID --> %', user_id; +RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id; + + Still another variant is to write RAISE USING or RAISE + level USING and put + everything else into the USING list. + + + + The last variant of RAISE has no parameters at all. + This form can only be used inside a BEGIN block's + EXCEPTION clause; + it causes the error currently being handled to be re-thrown to the + next enclosing block. + + + + If no condition name nor SQLSTATE is specified in a + RAISE EXCEPTION command, the default is to use + RAISE_EXCEPTION (P0001). If no message + text is specified, the default is to use the condition name or + SQLSTATE as message text. + + + - RAISE EXCEPTION presently always generates - the same SQLSTATE code, P0001, no matter what message - it is invoked with. It is possible to trap this exception with - EXCEPTION ... WHEN RAISE_EXCEPTION THEN ... but there - is no way to tell one RAISE from another. + When specifying an error code by SQLSTATE code, you are not + limited to the predefined error codes, but can select any + error code consisting of five digits and/or upper-case ASCII + letters, other than 00000. It is recommended that + you avoid throwing error codes that end in three zeroes, because + these are category codes and can only be trapped by trapping + the whole category. + + @@ -4307,7 +4387,9 @@ $$ LANGUAGE plpgsql; The syntax of RAISE is considerably different from - Oracle's similar statement. + Oracle's statement, although the basic case RAISE + exception_name works + similarly. -- cgit v1.2.3