PostgreSQL Tutorial
Data Types
Querying & Filtering Data
Managing Tables
Modifying Data
Conditionals
Control Flow
Transactions & Constraints
Working with JOINS & Schemas
Roles & Permissions
Working with Sets
Subquery & CTEs
User-defined Functions
Important In-Built Functions
PostgreSQL PL/pgSQL
Variables & Constants
Stored Procedures
Working with Triggers
Working with Views & Indexes
Errors & Exception Handling
In PostgreSQL, exception handling is primarily accomplished using the PL/pgSQL language, which is a procedural language provided by PostgreSQL. Exception handling allows you to manage errors or unexpected events that arise during the execution of SQL statements.
Here's a basic outline on how to handle exceptions in PL/pgSQL:
BEGIN ... EXCEPTION ... END Block:
In PL/pgSQL, you wrap your main logic within a BEGIN ... END block. If an error occurs in the code between BEGIN and EXCEPTION, execution will jump to the EXCEPTION part, where you can handle the error.
Catching Specific Exceptions:
After the EXCEPTION keyword, you can specify which error you'd like to catch, and then provide the handling logic.
Logging Errors: You can log errors or take other actions like rolling back the transaction, re-raising the error, etc.
Here's a simple example of a function that divides two numbers and handles division by zero:
CREATE OR REPLACE FUNCTION divide_numbers(a float8, b float8)
RETURNS float8 AS $$
DECLARE
result float8;
BEGIN
result := a / b;
RETURN result;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'Attempted to divide by zero.';
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
When you call this function with a divisor of zero, it will catch the division_by_zero exception, raise a notice, and return null:
SELECT divide_numbers(5, 0);
Output:
NOTICE: Attempted to divide by zero.
Result:
NULL
The important part of the code above is:
EXCEPTION: The keyword that begins the exception-handling block.
WHEN division_by_zero: This catches the specific error when there's a division by zero.
RAISE NOTICE: This logs a message to notify about the error.
In real-world applications, your exception handling logic might include more sophisticated operations such as recording the error in a logging table, rolling back transactions, or even sending notifications.
Remember, exception handling should be used judiciously. Overuse can lead to more complex and harder-to-maintain code. It's generally advisable to handle only the exceptions that you anticipate and know how to deal with properly.
Try-catch block in PostgreSQL:
BEGIN...EXCEPTION...END block for exception handling.BEGIN
-- Some PL/pgSQL code here
EXCEPTION
WHEN others THEN
-- Handling code for any exception
END;
Handling errors in PL/pgSQL:
BEGIN
-- Some PL/pgSQL code here
EXCEPTION
WHEN others THEN
-- Handling code for any exception
END;
RAISE EXCEPTION in PostgreSQL:
CREATE OR REPLACE FUNCTION example_function() RETURNS VOID AS $$
BEGIN
-- Some PL/pgSQL code here
IF some_condition THEN
RAISE EXCEPTION 'Custom error message';
END IF;
END;
$$ LANGUAGE plpgsql;
Catching specific exceptions in PostgreSQL:
BEGIN
-- Some PL/pgSQL code here
EXCEPTION
WHEN unique_violation THEN
-- Handling code for unique constraint violation
WHEN others THEN
-- Handling code for any other exception
END;
Nested exception handling in PL/pgSQL:
BEGIN
-- Outer block
BEGIN
-- Inner block
-- Some PL/pgSQL code here
EXCEPTION
WHEN others THEN
-- Handling code for inner block exception
END;
EXCEPTION
WHEN others THEN
-- Handling code for outer block exception
END;
Error codes and messages in PostgreSQL:
BEGIN
-- Some PL/pgSQL code here
EXCEPTION
WHEN others THEN
-- Handling code with GET STACKED DIAGNOSTICS
GET STACKED DIAGNOSTICS
error_message = MESSAGE_TEXT,
error_code = SQLSTATE;
END;
Logging exceptions in PostgreSQL stored procedures:
CREATE OR REPLACE FUNCTION example_function() RETURNS VOID AS $$
BEGIN
-- Some PL/pgSQL code here
EXCEPTION
WHEN others THEN
-- Logging exception
INSERT INTO error_log(timestamp, error_message)
VALUES (NOW(), SQLERRM);
END;
$$ LANGUAGE plpgsql;