PostgreSQL Error Handling

Introduction

In this lab, you will explore error handling techniques in PostgreSQL using PL/pgSQL. You will learn how to raise notices, catch exceptions, log errors, and test your error handling logic. This lab is designed for beginners and will guide you through each step.

Raising Notices in a Function

In this step, you will learn how to raise notices within a PL/pgSQL function. Notices are informational messages that can be useful for debugging or providing feedback. They are less severe than errors and do not stop the function from running.

To start, connect to the PostgreSQL database as the postgres user:

sudo -u postgres psql

Now, let's create a function called greet that takes a name as input and raises a notice with a greeting message. Execute the following SQL command in the psql shell:

CREATE OR REPLACE FUNCTION greet(name TEXT)
RETURNS TEXT AS $$
BEGIN
  RAISE NOTICE 'Hello, %!', name;
  RETURN 'Greeting sent!';
END;
$$ LANGUAGE plpgsql;

This function takes a name argument of type TEXT. The RAISE NOTICE 'Hello, %!', name; line displays a notice message. The % is a placeholder that will be replaced by the value of the name variable. The function then returns the string 'Greeting sent!'.

Let's call the function with the name 'LabEx':

SELECT greet('LabEx');

You should see output similar to this:

NOTICE:  Hello, LabEx!
   greet
-----------
 Greeting sent!
(1 row)

The NOTICE message "Hello, LabEx!" is displayed, showing that the RAISE NOTICE statement was executed.

You can also use different levels of notices, such as RAISE WARNING and RAISE INFO. RAISE WARNING indicates a potential problem, while RAISE INFO provides less important information.

Let's modify the function to raise a warning if the name is empty:

CREATE OR REPLACE FUNCTION greet(name TEXT)
RETURNS TEXT AS $$
BEGIN
  IF name = '' THEN
    RAISE WARNING 'Name is empty!';
  ELSE
    RAISE NOTICE 'Hello, %!', name;
  END IF;
  RETURN 'Greeting sent!';
END;
$$ LANGUAGE plpgsql;

Now, if you call the function with an empty string:

SELECT greet('');

You will see a warning message:

WARNING:  Name is empty!
   greet
-----------
 Greeting sent!
(1 row)

This demonstrates how to use RAISE NOTICE and RAISE WARNING to provide feedback within a PL/pgSQL function.

Catching Exceptions in PL/pgSQL

In this step, you will learn how to catch exceptions in PL/pgSQL using the EXCEPTION block. Exception handling allows you to handle errors gracefully, preventing your function from crashing and providing informative error messages.

The basic structure of an exception block is:

BEGIN
  -- Code that might raise an exception
EXCEPTION
  WHEN exception_name THEN
    -- Code to handle the exception
END;

The BEGIN and END keywords define the block of code where exceptions might occur. The EXCEPTION keyword introduces the exception handling section. The WHEN clause specifies the type of exception to catch, and the code following THEN is executed when that exception occurs.

Let's create a function that attempts to divide a number by zero and catches the resulting exception. We'll name it safe_divide.

CREATE OR REPLACE FUNCTION safe_divide(numerator INTEGER, denominator INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN numerator / denominator;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Division by zero occurred!';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

This function takes two integer arguments, numerator and denominator. Inside the BEGIN block, it attempts to divide the numerator by the denominator. If the denominator is zero, a division_by_zero exception will be raised. The EXCEPTION block catches this exception, raises a notice, and returns NULL.

Now, let's call the function with a divisor of zero:

SELECT safe_divide(10, 0);

You should see output similar to this:

NOTICE:  Division by zero occurred!
 safe_divide
-------------
          NULL
(1 row)

The NOTICE message "Division by zero occurred!" is displayed, and the function returns NULL, indicating that the exception was caught and handled.

Now, let's call the function with valid inputs:

SELECT safe_divide(10, 2);

You should see output similar to this:

 safe_divide
-------------
           5
(1 row)

The function returns the correct quotient, 5, without raising any exceptions.

You can also catch other types of exceptions, such as numeric_value_out_of_range, null_value_not_allowed, and others. The others exception handler catches any exception that is not explicitly handled by a previous WHEN clause.

Let's modify the function to catch any exception and return -1:

CREATE OR REPLACE FUNCTION safe_divide(numerator INTEGER, denominator INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN numerator / denominator;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Division by zero occurred!';
    RETURN NULL;
  WHEN OTHERS THEN
    RAISE NOTICE 'An unexpected error occurred: %', SQLERRM;
    RETURN -1;
END;
$$ LANGUAGE plpgsql;

In this modified function, if any exception other than division_by_zero occurs, the OTHERS exception handler will be executed. SQLERRM is a built-in variable that contains the error message associated with the exception.

Summary

In this lab, you explored error handling techniques in PostgreSQL using PL/pgSQL. You learned how to raise notices within functions using RAISE NOTICE, RAISE WARNING, and RAISE INFO to provide informational messages. You also learned how to catch exceptions using BEGIN...EXCEPTION...END blocks and log error details to a table for auditing and debugging. Finally, you practiced testing your error handling logic to ensure robustness.