Instruction
Great! Let's now move on to exception handling. You can catch errors/exceptions and recover from them by using a BEGIN block with an EXCEPTION clause.
If no error occurs, the EXCEPTION block will simply be ignored, and control will be passed to the next statement after END. In case an error occurs within the statements, control will pass to the EXCEPTION block, where the exception list will be searched for the first condition matching the error. The detailed list of all error codes is available in the official Postgres documentation.
The syntax is an extension of the BEGIN block syntax:
BEGIN
RAISE NOTICE 'Department budget per employee is: %', total_budget_for_dep / num_of_employees_in_dep;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'Caught division_by_zero exception!';
END;
In the example above, if the num_of_employees_in_dep is zero, the division_by_zero exception will be handled, and the following message will be printed out:
NOTICE: Caught division_by_zero exception!
Exercise
Complete the predefined template so the final script will handle the null_value_not_allowed exception and raises the following notice:
Caught null_value_not_allowed exception!
Stuck? Here's a hint!
Use the following block:
EXCEPTION
WHEN null_value_not_allowed THEN
<action>
