In this lab, you will explore error handling techniques in PostgreSQL using its procedural language, PL/pgSQL. You will learn how to create functions that can handle potential errors gracefully instead of crashing. The lab covers raising informational messages, catching specific exceptions like "division by zero," and logging error details to a database table for later review. By the end, you will understand how to build more robust and reliable database functions.
In this step, you will learn to use the RAISE statement in a PL/pgSQL function. The RAISE statement is useful for providing informational messages, warnings, or debugging output without stopping the function's execution.
First, connect to the PostgreSQL interactive terminal, psql, as the postgres user. All subsequent SQL commands in this lab will be run inside this terminal.
sudo -u postgres psqlYou will now see the psql prompt, which looks like postgres=#.
Let's create a function named greet that takes a name as input and raises a NOTICE. Notices are low-priority messages.
Execute the following SQL command in the psql shell:
CREATE OR REPLACE FUNCTION greet(name TEXT)
RETURNS TEXT AS $$
BEGIN
RAISE NOTICE 'Greeting function called with name: %', name;
RETURN 'Hello, ' || name;
END;
$$ LANGUAGE plpgsql;This function takes a name argument. The RAISE NOTICE '...', name; line generates a notice message. The % is a placeholder that is replaced by the value of the name variable.
Now, call the function to see it in action:
SELECT greet('LabEx');The output shows both the notice message and the function's return value:
NOTICE: Greeting function called with name: LabEx
greet
-----------
Hello, LabEx
(1 row)You can also raise messages with a higher severity level, like WARNING. Let's modify the function to raise a warning if the input name is empty.
CREATE OR REPLACE FUNCTION greet(name TEXT)
RETURNS TEXT AS $$
BEGIN
IF name IS NULL OR name = '' THEN
RAISE WARNING 'Input name is empty or NULL.';
RETURN 'Hello, stranger';
ELSE
RAISE NOTICE 'Greeting function called with name: %', name;
RETURN 'Hello, ' || name;
END IF;
END;
$$ LANGUAGE plpgsql;Now, call the function with an empty string to trigger the warning:
SELECT greet('');This time, you will see a WARNING message instead of a NOTICE:
WARNING: Input name is empty or NULL.
greet
------------------
Hello, stranger
(1 row)You have successfully used RAISE to generate different types of messages from within a function.
In this step, you will learn how to handle runtime errors, known as exceptions. An unhandled exception will immediately terminate your function. You will first create a function that fails, and then modify it to catch the exception gracefully.
Let's create a function simple_divide that performs integer division. This function has a potential flaw: it will crash if you try to divide by zero.
CREATE OR REPLACE FUNCTION simple_divide(numerator INTEGER, denominator INTEGER)
RETURNS INTEGER AS $$
BEGIN
RETURN numerator / denominator;
END;
$$ LANGUAGE plpgsql;First, call the function with valid inputs to confirm it works correctly:
SELECT simple_divide(10, 2);The output will be the result of the division:
simple_divide
---------------
5
(1 row)Now, call the function with a zero denominator to trigger an exception:
SELECT simple_divide(10, 0);This command will fail and return an error message. The function execution is aborted.
ERROR: division by zero
CONTEXT: PL/pgSQL function simple_divide(integer,integer) line 3 at RETURNThis is an unhandled exception. To prevent the function from crashing, you can use an EXCEPTION block. Let's create a new function safe_divide that includes error handling.
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 'Error: Cannot divide by zero.';
RETURN NULL;
END;
$$ LANGUAGE plpgsql;Here's how the EXCEPTION block works:
BEGIN...END;: This defines a block of code.EXCEPTION: This keyword starts the exception handling section.WHEN division_by_zero THEN: This specifies that the following code should run only when a division_by_zero error occurs.NULL instead of crashing.Now, call the new safe_divide function with a zero denominator:
SELECT safe_divide(10, 0);This time, the function does not crash. It catches the exception, displays your custom notice, and returns NULL.
NOTICE: Error: Cannot divide by zero.
safe_divide
-------------
(1 row)You have successfully caught a specific exception and controlled the function's output.
Catching exceptions is good, but for auditing and debugging, it's often better to log errors to a persistent location, like a table. In this step, you will modify the safe_divide function to write error details into the error_log table that was created during the lab setup.
First, let's examine the structure of the error_log table using the \d command in psql.
\d error_logYou will see the table's columns, which include an auto-incrementing id, a timestamp, the error message, and the function_name.
Table "public.error_log"
Column | Type | Collation | Nullable | Default
---------------+-----------------------------+-----------+----------+----------------------------------------------------
id | integer | | not null | nextval('error_log_id_seq'::regclass)
timestamp | timestamp without time zone | | | (now() AT TIME ZONE 'utc'::text)
message | text | | |
function_name | text | | |
Indexes:
"error_log_pkey" PRIMARY KEY, btree (id)Now, let's modify the safe_divide function. In the EXCEPTION block, you will add an INSERT statement to log the error before returning.
CREATE OR REPLACE FUNCTION safe_divide(numerator INTEGER, denominator INTEGER)
RETURNS INTEGER AS $$
BEGIN
RETURN numerator / denominator;
EXCEPTION
WHEN division_by_zero THEN
INSERT INTO error_log (message, function_name)
VALUES ('Division by zero occurred!', 'safe_divide');
RAISE NOTICE 'Error: Cannot divide by zero. Details logged.';
RETURN NULL;
END;
$$ LANGUAGE plpgsql;Now, call the function again to trigger the error and the new logging logic:
SELECT safe_divide(10, 0);The function will return NULL as before, but it has also performed a database write. To verify this, query the error_log table to see the new entry.
SELECT function_name, message FROM error_log;The output should show the logged error:
function_name | message
---------------+----------------------------
safe_divide | Division by zero occurred!
(1 row)This confirms that your error handling logic is now capable of persisting error information for later analysis.
So far, you have handled a very specific error: division_by_zero. But what about other, unexpected errors? PL/pgSQL provides a general-purpose OTHERS exception handler to catch any error that hasn't been caught by a more specific WHEN clause.
In this step, you will enhance the safe_divide function to handle any possible exception. You will also use two special variables, SQLSTATE (the SQL error code) and SQLERRM (the SQL error message), to log more detailed information.
Let's modify the safe_divide function one last time.
CREATE OR REPLACE FUNCTION safe_divide(numerator INTEGER, denominator INTEGER)
RETURNS INTEGER AS $$
BEGIN
RETURN numerator / denominator;
EXCEPTION
WHEN division_by_zero THEN
INSERT INTO error_log (message, function_name)
VALUES ('Division by zero occurred!', 'safe_divide');
RAISE NOTICE 'Error: Cannot divide by zero. Details logged.';
RETURN NULL;
WHEN OTHERS THEN
INSERT INTO error_log (message, function_name)
VALUES ('An unexpected error occurred: ' || SQLSTATE || ' - ' || SQLERRM, 'safe_divide');
RAISE NOTICE 'An unexpected error occurred. Details logged.';
RETURN -1;
END;
$$ LANGUAGE plpgsql;The new WHEN OTHERS block will catch any other error. It constructs a detailed message using the SQLSTATE and SQLERRM variables and inserts it into the log. It then returns -1 to signal that a generic error occurred.
While our current function can only trigger a division_by_zero error, this structure makes it much more robust against future modifications or unexpected inputs. For example, if the input types were changed to a type that could overflow, the OTHERS block would catch the numeric_value_out_of_range error.
Let's test the division_by_zero path again to ensure it still works as expected. First, clear the log table for a clean test.
TRUNCATE error_log;Now, call the function:
SELECT safe_divide(10, 0);Finally, check the log. It should contain the specific "Division by zero" message, confirming that the first WHEN block was correctly chosen.
SELECT message FROM error_log; message
----------------------------
Division by zero occurred!
(1 row)You have now built a resilient function that handles both specific, anticipated errors and general, unexpected ones.
In this lab, you have learned the fundamentals of error handling in PostgreSQL's PL/pgSQL. You started by using the RAISE statement to issue NOTICE and WARNING messages for debugging and informational purposes. You then progressed to handling runtime exceptions by creating a function that could fail and then implementing a BEGIN...EXCEPTION...END block to catch a specific division_by_zero error. Building on this, you enhanced the function to log error details to a database table, providing a persistent record for auditing. Finally, you made the function more robust by adding a general WHEN OTHERS handler to catch any unexpected errors, ensuring your function can fail gracefully under various conditions.
