From: Janning Vygen <vygen@gmx.de>
Organization: PLANWERK 6
To: <btober@seaworthysys.com>, <pgsql-sql@postgresql.org>
Subject: Re: Informing end-user of check constraint rules
Date: Sun, 22 Jun 2003 22:34:39 +0200
User-Agent: KMail/1.5.1
References: <64845.66.212.203.144.1056285922.squirrel@$HOSTNAME>
In-Reply-To: <64845.66.212.203.144.1056285922.squirrel@$HOSTNAME>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200306222234.39685.vygen@gmx.de>

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=
=20
like below. then you have everything at one place. I think postgres should=
=20
have better ways to report errors but i am not a database guru and dont kno=
w=20
how other databases do their error handling.=20

Maybe its better to have some kind of "middleware" to keep the business log=
ic=20
and use the database just to store data... i thought about it a lot and tri=
ed=20
to find relevant informations about how to model data/businesslogic/fronten=
d=20
in a convienient way...=20

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

what i like most is not having good error message but you can show all erro=
rs=20
at once.

kind regards
janning

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

CREATE FUNCTION tg_user_col_check () RETURNS TRIGGER AS '
  DECLARE
    var_errmsg   text :=3D ''TIPPER'';
    var_error    boolean;
    rec_any      RECORD;
    var_count    int4;
    var_maxmitgl int4 :=3D 1000;   ---------------
    -- email     --
    ---------------
    NEW.email :=3D btrim(NEW.email);

    IF NEW.email !~ ''^[A-Za-z0-9.@_-]+$'' THEN
      var_error  :=3D ''true'';
      var_errmsg :=3D var_errmsg || ''#name:''
      || ''Die E-Mail Adresse darf nur aus Buchstaben, Zahlen und einigen=
=20=20
Sonderzeichen ("_", "-", "@", ".") bestehen. '';
    END IF;

    IF length(NEW.name) < 3 THEN
      var_error  :=3D ''true'';
      var_errmsg :=3D var_errmsg || ''#name:''
      || ''Der Benutzername muss mindestens drei Zeichen lang sein";
    END IF;

    IF length(NEW.email) > 50  THEN
      var_error  :=3D ''true'';
      var_errmsg :=3D var_errmsg || ''#email:''
      || ''Die E-Mail Adresse darf nicht l=E4nger als 50 Buchstaben sein'';
    END IF;

    IF var_error THEN
      RAISE EXCEPTION ''%'', var_errmsg;
    END IF;
    RETURN NEW;
  END;
' language 'plpgsql';