PostgreSQL stored Procedure (5)-Exception error handling

1. Exception error Handling

In the Pl/pgsql function, if there is no exception capture, the function exits directly when an error occurs, and the things associated with it are rolled back. We can catch and recover the exception by using the Begin block with the exception clause. See the following declaration form:

[ <> ] [ DECLARE
    declarations ] BEGIN statements
EXCEPTION
  WHEN condition [ OR condition ... ] THEN handler_statements
  WHEN condition [ OR condition ... ] THEN          handler_statements END;                    

If no error occurs, only the statements in the begin block will be executed normally, but once any of these statements has an error, the subsequent statements will be skipped and jump directly to the beginning of the exception block. The system searches the list of exception conditions, finds the first condition that matches the exception, executes the corresponding handler_statements if a match is found, and then executes the next statement of end. If no match is found, the error is continued to be thrown out, and the result is exactly the same as no exception clause. If a new error occurs in a statement in handler_statements, it cannot be captured by the exception clause, but will continue to propagate outwards and be captured and processed by the exception clause on its outer layer.

In PostgreSQL, you can use the Raise statement to report information and throw errors, which are declared in the following form:

 Level ' format ' [, expression [, ... ]];

The levels included are debug (write to server log), log (write information to server log, priority higher), info, notice, and warning (write information to server log and forward to client application, with a gradual increase in priority) and exception throw an error (usually exiting the current transaction). Whether the information for a priority level is reported to the client or to the server log, or two, is controlled by both the Log_min_messages and client_min_messages system initialization parameters.
In the format section,% is represented as a placeholder, and its actual value is replaced only by subsequent variables when the raise command executes, and if you want to represent% itself in format, you can use a percentage of percent, as shown in the following example:
RAISE NOTICE ' calling cs_create_job (%) ', the value of the V_JOB_ID;--V_JOB_ID variable will be replaced by% in format.
RAISE EXCEPTION ' inexistent ID---% ', user_id;

In simple terms:

--Throw an exception
RAISE EXCEPTION ‘You have a problem. The repair! ‘;
-- Use SQLERRM to display error messages.
RAISE EXCEPTION ‘(%)‘, SQLERRM; 

See the example below:

CREATE OR REPLACE FUNCTION GETEXCEPTION(v_phone text) RETURNS void AS
$$
BEGIN
     IF v_phone = ‘iphone‘ THEN
        RAISE EXCEPTION ‘You have a problem. The repair! ‘;
     ELSIF v_phone = ‘samsung‘ THEN
        RAISE EXCEPTION ‘You will explode, stay away from you! ‘;
     Else
        RETURN;
     END IF;
     EXCEPTION
     WHEN others THEN
     RAISE EXCEPTION ‘(%)‘, SQLERRM;
END
$$ LANGUAGE PLPGSQL; 

Reference: http://www.postgres.cn/docs/9.3/plpgsql-errors-and-messages.html

PostgreSQL stored Procedure (5)-Exception error handling