From 3d4890c0c5d27dfdf7d1a8816d7bdcdba3c39d21 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 18 Jul 2011 14:46:27 -0400 Subject: Add GET STACKED DIAGNOSTICS plpgsql command to retrieve exception info. This is more SQL-spec-compliant, more easily extensible, and better performing than the old method of inventing special variables. Pavel Stehule, reviewed by Shigeru Hanada and David Wheeler --- doc/src/sgml/plpgsql.sgml | 121 ++++++++++++++++++++++++++++++++++++++++------ 1 file changed, 106 insertions(+), 15 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 20bc9910836..08c3658d5e3 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1387,11 +1387,11 @@ EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname) command, which has the form: -GET DIAGNOSTICS variable = item , ... ; +GET CURRENT DIAGNOSTICS variable = item , ... ; This command allows retrieval of system status indicators. Each - item is a key word identifying a state + item is a key word identifying a status value to be assigned to the specified variable (which should be of the right data type to receive it). The currently available status items are ROW_COUNT, the number of rows @@ -2522,16 +2522,6 @@ END; - - Within an exception handler, the SQLSTATE - variable contains the error code that corresponds to the - exception that was raised (refer to for a list of possible error - codes). The SQLERRM variable contains the - error message associated with the exception. These variables are - undefined outside exception handlers. - - Exceptions with <command>UPDATE</>/<command>INSERT</> @@ -2568,11 +2558,112 @@ LANGUAGE plpgsql; SELECT merge_db(1, 'david'); SELECT merge_db(1, 'dennis'); - This example assumes the unique_violation error is caused by - the INSERT, and not by an INSERT trigger function - on the table. + + This coding assumes the unique_violation error is caused by + the INSERT, and not by, say, an INSERT in a + trigger function on the table. More safety could be had by using the + features discussed next to check that the trapped error was the one + expected. + + + Obtaining information about an error + + + Exception handlers frequently need to identify the specific error that + occurred. There are two ways to get information about the current + exception in PL/pgSQL: special variables and the + GET STACKED DIAGNOSTICS command. + + + + Within an exception handler, the special variable + SQLSTATE contains the error code that corresponds to + the exception that was raised (refer to + for a list of possible error codes). The special variable + SQLERRM contains the error message associated with the + exception. These variables are undefined outside exception handlers. + + + + Within an exception handler, one may also retrieve + information about the current exception by using the + GET STACKED DIAGNOSTICS command, which has the form: + + +GET STACKED DIAGNOSTICS variable = item , ... ; + + + Each item is a key word identifying a status + value to be assigned to the specified variable (which should be + of the right data type to receive it). The currently available + status items are: + + + Error diagnostics values + + + + Name + Type + Description + + + + + RETURNED_SQLSTATE + text + the SQLSTATE error code of the exception + + + MESSAGE_TEXT + text + the text of the exception's primary message + + + PG_EXCEPTION_DETAIL + text + the text of the exception's detail message, if any + + + PG_EXCEPTION_HINT + text + the text of the exception's hint message, if any + + + PG_EXCEPTION_CONTEXT + text + line(s) of text describing the call stack + + + +
+ + + + If the exception did not set a value for an item, an empty string + will be returned. + + + + Here is an example: + +DECLARE + text_var1 text; + text_var2 text; + text_var3 text; +BEGIN + -- some processing which might cause an exception + ... +EXCEPTION WHEN OTHERS THEN + GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT, + text_var2 = PG_EXCEPTION_DETAIL, + text_var3 = PG_EXCEPTION_HINT; +END; + + + -- cgit v1.2.3