postgres procedure raise exception code example

Example: postgresql raise exception

-- Simple exception:
RAISE EXCEPTION 'This user role does not exist.';

-- This example will abort the transaction with the given error message and 
-- hint:
RAISE EXCEPTION 'Nonexistent ID --> %', user_id
      USING HINT = 'Please check your user ID';

-- These two examples show equivalent ways of setting the SQLSTATE:
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';

-- There is a second RAISE syntax in which the main argument is the condition 
-- name or SQLSTATE to be reported, for example:
RAISE division_by_zero;
RAISE SQLSTATE '22012';

-- In this syntax, USING can be used to supply a custom error message, detail, 
-- or hint. Another way to do the earlier example is
RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;

Tags:

Misc Example