Database Reference
In-Depth Information
code --> %', digits USING HINT = 'Zip code is
more than 9 digits.', ERRCODE = 'P9997';
ELSIF length(digits) > 5 AND length(digits) <
9 THEN
RAISE EXCEPTION 'Zip code cannot be
processed --> %', digits USING HINT = 'Zip code
abnormal length.', ERRCODE = 'P9996';
ELSE
RETURN true;
END IF;
END;
$$ LANGUAGE plpgsql;
The
ERRCODE
values are defined by the developer. In this example, I used the gen-
eral PL/pgSQL error code value (
P0001
or
plpgsql_error
), started at the top of
the range (
P9999
) of errors, and decremented for each type of error that I wished to
expose. This is a very simplistic technique designed to prevent overlap in the future
from error codes used by PL/pgSQL. You are free to invent any error codes you like,
but would be well advised to avoid those already listed in the documentation at
ht-
A sample function (
error_trap_report
) has been provided in the accompanying
code that you can easily modify to determine the error code constant that is being
thrown by any given error number. For PL/pgSQL functions, the error constant is
plpgsql_error
(
P0001
) by default.
The following is the code used to capture any errors thrown in the previous example:
CREATE OR REPLACE FUNCTION
get_us_zip_validation_status(zipcode text)
returns text
AS
$$
BEGIN
SELECT validate_us_zip(zipcode);
RETURN 'Passed Validation';
EXCEPTION
