Postgresql PL/PgSQL — Raise User-Defined Exception With Custom SQLERRM

An exceptions is an event that occurs when the program is interruptet from its normal flow. Errors occur on all servers in the world, in different languages and different context.

In PostgreSQL, as in any database management system, there are several types of errors that can occur when executing a function. Some of the most common errors in PostgreSQL are:

Syntax errors: Occur when there is a problem with the structure of a query or SQL statement, for example, if a comma or parenthesis is missing.

Data type errors:These occur when you try to perform an operation with an incompatible data type or you try to assign a value to a variable that doesn’t match its data type.

Constraint violation errors: Occur when you try to insert, update, or delete data that violates a constraint, such as a primary key or a unique constraint.

Transaction errors: Occur when attempting to perform an operation that cannot be completed due to a pending or active transaction.

Permission errors: Occur when the user executing a function does not have the necessary permissions to perform the desired operation, such as reading or writing to a table or executing a function.

Connection errors: Occur when a connection to the database cannot be established or when the connection is dropped during the execution of an operation.

If you try to insert a value that already exists into a column with a unique constraint, you will get a unique constraint violation error. Here is an example of how to produce this error:

CREATE TABLE example_table (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE
);

INSERT INTO example_table (name) VALUES ('Jorge');
INSERT INTO example_table (name) VALUES ('Jorge');

This will produce the following error:

ERROR:  duplicate key value violates unique constraint "example_table_name_key"
DETAIL:  Key (name)=(Jorge) already exists.

Postgres can capture an error inside a function using the EXCEPTIONblock in PL/pgSQL. The EXCEPTIONblock allows you to handle errors that may occur during the execution of a function.

When an error occurs, the execution of the block or the function jumps to the EXCEPTIONblock, where you can catch the error and handle it as needed. You can catch specific types of errors, such as syntax errors, undefined column errors, or foreign key constraint violations, or you can catch all types of errors using the WHEN OTHERS THEN clause.

ERROR:  [A0001]: First number greater than second
DETAIL:  Where: función PL/pgSQL inline_code_block en la línea 4 en RAISE

This code is an anonymous PL/pgSQL block that includes an if conditional and an exception clause. In the body of the if conditional, a custom error is raised using the RAISE EXCEPTION USING clause.

The custom error has an error code of A0001and an error message of 'First number greater than second'.

The WHEN OTHERS THEN exception clause catches any exception other than the custom exception raised by the RAISE EXCEPTION USING.

One approach would be to define custom error codes and messages using the RAISEstatement, which will allow you to raise specific errors with unique codes and messages that can be handled globally.

For that, it would be a very good option to create a table to handle the different types of errors and categorize them if necessary.

CREATE TABLE custom_error_messages (
  error_code TEXT PRIMARY KEY,
  error_message TEXT NOT NULL
);

INSERT INTO custom_error_messages (error_code, error_message)
VALUES
  ('A0001', 'Product name is required'),
  ('A0002', 'Product price must be greater than zero'),
  ('A0003', 'Product code is invalid');

Then with a function that can receive the code of the registered error as a parameter and throw an exception

CREATE OR REPLACE FUNCTION raise_custom_error(p_error_code TEXT)
RETURNS VOID AS $$
DECLARE
  v_error_message TEXT;
BEGIN
  -- Find error message in the custom_error_messages table 
  SELECT error_message INTO v_error_message
  FROM custom_error_messages
  WHERE error_code = p_error_code;

  -- Raise exception with the retrieved error message
  RAISE EXCEPTION USING 
            ERRCODE = p_error_code, 
            MESSAGE = v_error_message;
END;
$$ LANGUAGE plpgsql;

Function is executed

select raise_custom_error('A0001')

Output

ERROR [A0001]: Product name is required

Example

Let’s use a real life example, a products table is created

CREATE TABLE products (
 id SERIAL PRIMARY KEY,
 name varchar NULL,
 code int4 NULL,
 price numeric NULL
);

Define a function that uses custom error codes and messages

CREATE OR REPLACE FUNCTION create_product(product_name text, product_code text, product_price numeric)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF product_name IS NULL OR product_name = '' THEN
    perform raise_custom_error('A0001');
  END IF;

  IF product_price <= 0 THEN
    perform raise_custom_error('A0002');
  END IF;

  -- insert the product into the database
  INSERT INTO products (name, code, price) VALUES (product_name, product_code, product_price);
END;
$function$;

Unexpected Postgres errors

Postgres say here:

All messages emitted by the PostgreSQL server are assigned five-character error codes that follow the SQL standard’s conventions for “SQLSTATE” codes.

So far everything is working fine but there is something that we must not ignore, which are the “unexpected errors” that Postgresql can emit and that we must verify, if they occur they will be thrown to the backend without handling it.

For this, the best option to handle errors, the codes have five characters and it is recommended to use the letter ‘A’ followed by something identifying that is easy to use and read in the future. For example:

A0001 —> Any error

in this way an error defined by the user can be differentiated from a system error.

CREATE OR REPLACE FUNCTION handle_error(p_sqlstate text, p_sqlerrm text, p_function_name text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
begin
  if p_sqlstate not like 'A%' then
     p_sqlstate := '99999';
     p_sqlerrm := 'Unexpected error';
  end if;

  raise exception using ERRCODE = p_sqlstate,
                        MESSAGE = p_sqlerrm,
                        HINT = p_sqlerrm || ' in function ' || p_function_name;
end;
$function$;

A handle_error function is created that handles the error codes and will report only custom errors and for system errors a common error.

This code is then added to the above create_product procedure in the exception block, to handle user vs. postgres errors

CREATE OR REPLACE FUNCTION create_product(product_name text, product_code text, product_price numeric)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF product_name IS NULL OR product_name = '' THEN
    perform raise_custom_error('A0001');
  END IF;

  IF product_price <= 0 THEN
    perform raise_custom_error('A0002');
  END IF;

  -- insert the product into the database
  INSERT INTO products (name, code, price) VALUES (product_name, product_code, product_price);
EXCEPTION
  WHEN OTHERS THEN
      PERFORM handle_error(SQLSTATE, sqlerrm, 'create_product');
END;
$function$;

With this, prevent unexpected errors from showing in our backend unexpectedly

Conclusion

I demonstrate how to use Sequelize to create a custom error handling system and a simple product creation API.

You can find the complete code for this project on my GitHub repository.

I hope you find it helpful!