Re: Informing end-user of check constraint rules - Mailing list pgsql-sql

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?
>
> For instance, say that I wanted to use a RE to check e-mail address
> format validity and then the data entry clerk typed in invalid data. My
> understanding is that when the check constraint returns FALSE, the row
> will not insert, and an ExecAppend: rejected due to CHECK constraint
> "table_column " exception is raised. That at least tells the column
> (albeit in language that would scare the computer-phobe), but I like the
> exception message to tell the end user what the format is supposed to be.
> Is my only option to have the end-user application (as opposed to the
> database) inform the end-user what the correct data format is? If THAT is
> so, then it seems I might as well also perform the data formatting
> validation in the application, too, so that at least they'ld both be in
> the same place and not two separate places.
>
> What I'd like is to be able to specify some kind of error message telling
> the user what the correct format should be, and since the proper format
> is specified in the database, i.e., in the check constraint, it seems
> that the proper place to raise an exception providing the remedial
> instructions would also be in the database.

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.

Maybe its better to have some kind of "middleware" to keep the business logic
and use the database just to store data... i thought about it a lot and tried
to find relevant informations about how to model data/businesslogic/frontend
in a convienient way...

here is an example to check different columns and return an explanation of on
or more errors. of course your frontend has to parse this errormsg for the
relevant part shown to the user. its just copied but a little bit modified
code from a working example. but this code below isn't tested and
errormessages are in german.

what i like most is not having good error message but you can show all errors
at once.

kind regards
janning

CREATE TRIGGER tg_user BEFORE INSERT OR UPDATE ON USER FOR EACH ROW EXECUTE
PROCEDURE tg_user_col_check();

CREATE FUNCTION tg_user_col_check () RETURNS TRIGGER AS ' DECLARE   var_errmsg   text := ''TIPPER'';   var_error
boolean;  rec_any      RECORD;   var_count    int4;   var_maxmitgl int4 := 1000;   ---------------   -- email     --
---------------  NEW.email := btrim(NEW.email); 
   IF NEW.email !~ ''^[A-Za-z0-9.@_-]+$'' THEN     var_error  := ''true'';     var_errmsg := var_errmsg || ''#name:''
 || ''Die E-Mail Adresse darf nur aus Buchstaben, Zahlen und einigen   
Sonderzeichen ("_", "-", "@", ".") bestehen. '';   END IF;
   IF length(NEW.name) < 3 THEN     var_error  := ''true'';     var_errmsg := var_errmsg || ''#name:''     || ''Der
Benutzernamemuss mindestens drei Zeichen lang sein";   END IF; 
   IF length(NEW.email) > 50  THEN     var_error  := ''true'';     var_errmsg := var_errmsg || ''#email:''     || ''Die
E-MailAdresse darf nicht länger als 50 Buchstaben sein'';   END IF; 
   IF var_error THEN     RAISE EXCEPTION ''%'', var_errmsg;   END IF;   RETURN NEW; END;
' language 'plpgsql';