Re: Informing end-user of check constraint rules

> Am Sonntag, 22. Juni 2003 14:45 schrieb btober@seaworthysys.com:
>> I have not used column check constraints before, but I'd like to
>> start using then and so would I'll like to know if there is a direct
>> way to provide feedback to the end user about data validation rules
>> expressed in column check constraints?
>>
> you can use a trigger on insert and write your own error handling
> function  like below. then you have everything at one place. I think
> postgres should  have better ways to report errors but i am not a
> database guru and dont know  how other databases do their error
> handling.
>

I came up with what I think is a pretty cool implementation idea, as
follows.

-- 1) Define a table as

CREATE TABLE regular_expression (   description character varying(48) NOT NULL,   regular_expression character
varying(128),  user_message text
 
) WITHOUT OIDS;

-- with sample data:

INSERT INTO regular_expression VALUES ('Social Security Number',
'^\\d{3}-\\d{2}-\\d{4}$', 'Social Security Number must consist of the
pattern: nnn-nn-nnnn, where "n" is a digit.');

INSERT INTO regular_expression VALUES ('US Telephone Number',
'^[2-9]\\d{2}-\\d{3}-\\d{4}', 'US Telephone numbers must consist of the
pattern aaa-eee-nnnn, optionally followed by extra extension number,
where aaa is the three-digit area code, eee is the three digit exchange
code, and nnnn is the four digit number.');

INSERT INTO regular_expression VALUES ('Internet E-Mail Address',
'[a-z0-9_]+([\\\\-\\\\.][a-z0-9_]+)*@[a-z0-9_]+([\\\\-\\\\.][a-z0-9_]+)+',
'Internet E-Mail Addresses are typically of the form nnnnn@dddddd.ttt,
where, "nnnnn" is the user name, "dddddd" is the Internet domain name,
and "ttt" is the three character top-level domain name.');

INSERT INTO regular_expression VALUES ('US ZIP Code',
'^\\d{5}-\\d{4}|\\d{5}$', 'US Postal ZIP Codes are of the form nnnnn or
nnnnn-nnnn, where "n" is any digit.');

COMMENT ON TABLE regular_expression IS 'This table defines regular
expressions used in the application.';

-- 2) Define the following function to be used as a generic CHECK
constraint:

CREATE FUNCTION public.check_pattern(varchar, varchar) RETURNS bool AS '
DECLARE l_value ALIAS FOR $1; l_pattern ALIAS FOR $2; l_row RECORD;
BEGIN IF l_value IS NOT NULL THEN    IF EXISTS(SELECT 1 FROM public.regular_expression WHERE
UPPER(description) = UPPER(l_pattern)) THEN      SELECT INTO l_row regular_expression, user_message FROM
public.regular_expression WHERE UPPER(description) =
UPPER(l_pattern);      IF NOT (l_value ~ l_row.regular_expression) THEN        RAISE EXCEPTION ''Invalid %. %'',
l_pattern,l_row.user_message;      END IF;    END IF; END IF; RETURN TRUE;
 
END;
'  LANGUAGE 'plpgsql' VOLATILE;


-- 3) Define any check constraint you want similar to:

CREATE TABLE person ( e_mail_address varchar(128), social_security_no varchar(11),
--    [...other column defs...] CONSTRAINT person_e_mail_address CHECK (check_pattern(e_mail_address,
'Internet E-Mail Address')), CONSTRAINT person_social_security_no CHECK
(check_pattern(social_security_no, 'Social Security Number'))
) WITHOUT OIDS;



The only hitch I've run into so far is that when I want to do an ALTER
TABLE to ADD a CHECK CONSTRAINT this way when there is existing data, I
need to temporarily CREATE OR REPLACE the check_pattern function with the
RAISE EXCEPTION line commented out because for some reason that
particular exception gets raised in the process of trying to add the
constraint, and so the ADD CONSTRAINT command fails. After the constraint
is successfully added with the function's RAISE EXCEPTION call commented
out, then I re-CREATE OR REPLACE the check_pattern function with the
RAISE EXCEPTION line restored.

Seems to work great in preliminary testing.

~Berend Tober