Database Reference
In-Depth Information
Kirk L. Roybal
NOTICE: prefix. firstname mi lastname,
suffix.: Mr. Kirk L. Roybal, Author.
format_us_full_name_debug
-----------------------------
Mr. Kirk L. Roybal, Author.
(1 row)
Throwing exceptions
The
RAISE
command takes several other operators than
NOTICE
. It will also throw
exceptions that are intended for the calling code to catch. The following is an ex-
ample of creating an exception:
CREATE OR REPLACE FUNCTION
validate_us_zip(zipcode TEXT)
RETURNS boolean
AS $$
DECLARE
digits text;
BEGIN
-- remove anything that is not a digit (POSIX
compliantly, please)
digits := (SELECT
regexp_replace(zipcode,'[^[:digit:]]','','g'));
IF digits = '' THEN
RAISE EXCEPTION 'Zipcode does not contain
any digits --> %', digits USING HINT = 'Is this
a US zip code?', ERRCODE = 'P9999';
ELSIF length(digits) < 5 THEN
RAISE EXCEPTION 'Zipcode does not contain
enough digits --> %', digits USING HINT = 'Zip
code has less than 5 digits.', ERRCODE =
'P9998';
ELSIF length(digits) > 9 THEN
RAISE EXCEPTION 'Unnecessary digits in zip
