Skip site navigation (1) Skip section navigation (2)

Re: using functions to generate custom error messages

From: "Reshat Sabiq" <sabiq(at)purdue(dot)edu>
To: "'Joel Rodrigues'" <borgempath(at)Phreaker(dot)net>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: using functions to generate custom error messages
Date: 2002-11-16 05:54:56
Message-ID: 000001c28d34$b4dd8f30$d080d380@main (view raw or flat)
Thread:
Lists: pgsql-novice
I think it looks good. But my preference is to keep as little as
possible in the DB, in case it needs to be moved to a different DB
vendor's product.
That way one's CGI or Java can be deployed right off, w/o having to
re-write stored procedures, etc. And there are real companies out there
struggling to switch to a different DB because of things like that.
I am aware of the other extreme: DB admin protects the data and sets
maximum possible restrictions on access, ADs relying on available stored
procedures, and limited access. I tend to disagree for portability
reasons. But if somebody offers a convincing argument in favor of doing
so, I could change my humble opinion.

My 2 cents,
r.

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Joel Rodrigues
Sent: Thursday, November 14, 2002 11:31 PM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] using functions to generate custom error messages

[I just sent this using the wrong e-mail address thrice 
(oops !), so assuming it won't show up, here it comes again...]

Hello,

Would it be considered good practice to use functions to 
generate custom error messages ?

The only odd thing about it I can see is that constraints have 
to be coded into the function, whether or not they've already 
been included in the table itself.

Here's an example.

CREATE TABLE amelie (
    rating    int CHECK (rating <= 10)
);


The results of using just this :

hypatia=# INSERT INTO amelie VALUES ('11');
ERROR:  ExecAppend: rejected due to CHECK constraint amelie_rating


Here is the function with the constraint included again (phrased 
differently):

CREATE FUNCTION maxten () RETURNS OPAQUE AS '
     BEGIN
         IF NEW.rating > 10 THEN
             RAISE EXCEPTION ''Alert ! Please enter a number 
between 0 and 10.'';
         END IF;

         RETURN NEW;
     END;
' LANGUAGE 'plpgsql';


And the trigger:

CREATE TRIGGER trigtest BEFORE INSERT OR UPDATE
     ON amelie
     FOR EACH ROW
     EXECUTE PROCEDURE maxten();


The results of this:

hypatia=# INSERT INTO amelie VALUES ('11');
ERROR:  Alert ! Please enter a number between 0 and 10.

My goal with this is to stick with the principle of keeping 
"business logic" in the DBMS, avoiding elaborate error checking 
and other unnecessary verbosity in my Python CGI scripts. I can 
easily insert the function generated ERROR message into an xhtml 
template and display it in the web browser.

I do understand that there are other ways/places to do error 
checking, in the CGI script or in JavaScript, but this is 
how/where I'd like to do it.

BTW, does anyone know why \df doesn't find the function "maxten" 
that I created ?


hypatia=# \df maxten

                List of functions
  Result data type | Name | Argument data types
------------------+------+---------------------
(0 rows)


Cheers,
Joel



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)



In response to

Responses

pgsql-novice by date

Next:From: Rod KreislerDate: 2002-11-16 13:39:58
Subject: Re: using functions to generate custom error messages
Previous:From: Tom LaneDate: 2002-11-15 22:01:17
Subject: Re: Question on locale settings

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group