Thread: 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
I just realized there's another wee disadvantage to using functions to generate custom error messages. These are "Trigger Functions" which do not have arguments, so have to be defined per table, and are not reusable. And then there's the task of keeping track of all the functions you create... Still, there's something here. - Joel
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@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Joel Rodrigues
Sent: Thursday, November 14, 2002 11:31 PM
To: pgsql-novice@postgresql.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@postgresql.org)
Reshat, You have some points, however just as there is the possibility of switching RDBMS, there is a possibility that the frontend language will change as well. Furthermore, a greater possibility, I think, that multiple frontends under different platforms will access the one data source. Data integrity constraints MUST be in the database itself anyway (if not, then I suggest using MySQL...), otherwise you are hoping that multiple ADs are going to properly maintain integrity in the applications. IMHO, what Joel suggests is quite reasonable. Rod > -----Original Message----- > From: pgsql-novice-owner@postgresql.org > [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Reshat Sabiq > Sent: Saturday, November 16, 2002 12:55 AM > To: 'Joel Rodrigues'; pgsql-novice@postgresql.org > Subject: Re: [NOVICE] using functions to generate custom error messages > > > 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@postgresql.org > [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Joel Rodrigues > Sent: Thursday, November 14, 2002 11:31 PM > To: pgsql-novice@postgresql.org > Subject: [NOVICE] using functions to generate custom error messages > > > 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. >
Hi, The whole business of 'portability' as usually tossed around is highly suspect. One could argue that having as much as application logic as possible in one's carefully considered and chosen DBMS makes for a very portable application. It easily accommodates, as Rod says, multiple front ends under different platforms, with very little coding required of the developers of those front ends. I also agree that data integrity constraints should be in the DBMS itself. Well, 'reasonable' is good. I've just got to come up with a way to keep track of all the functions & triggers. I just wish Trigger Functions would accept arguments. Something for a future revision of PostgreSQL ? Tchau, Joel Rodrigues On Saturday, November 16, 2002, at 07:32 , Rod Kreisler wrote: > Reshat, > > You have some points, however just as there is the possibility > of switching > RDBMS, there is a possibility that the frontend language will change as > well. Furthermore, a greater possibility, I think, that > multiple frontends > under different platforms will access the one data source. > > Data integrity constraints MUST be in the database itself > anyway (if not, > then I suggest using MySQL...), otherwise you are hoping that > multiple ADs > are going to properly maintain integrity in the applications. > > IMHO, what Joel suggests is quite reasonable. > > Rod > >> -----Original Message----- >> From: pgsql-novice-owner@postgresql.org >> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Reshat Sabiq >> Sent: Saturday, November 16, 2002 12:55 AM >> To: 'Joel Rodrigues'; pgsql-novice@postgresql.org >> Subject: Re: [NOVICE] using functions to generate custom error >> messages >> >> >> 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@postgresql.org >> [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Joel Rodrigues >> Sent: Thursday, November 14, 2002 11:31 PM >> To: pgsql-novice@postgresql.org >> Subject: [NOVICE] using functions to generate custom error messages >> >> >> 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.
Thanks Rod, I certainly find it a must to use such features as referential integrity (even it costs a little), transaction support, custom objects, etc. which make postgreSQL stand apart from mySQL, etc. Such standard features as value checks, which I believe can be ported quite easily also don't arouse my doubts. I guess features that don't port well across DBs are stored procedures and consequently triggers. Those are the things I am referring to. For example, if we need to increment a field by a certain value once in every 2 weeks, would you suggest using stored procedures to accomplish it, or app logic? And in general, when would you say it's worth to use stored procedures? Thanks, r. -----Original Message----- From: Rod Kreisler [mailto:rod@23net.net] Sent: Saturday, November 16, 2002 9:02 AM To: Reshat Sabiq; 'Joel Rodrigues'; pgsql-novice@postgresql.org Subject: RE: [NOVICE] using functions to generate custom error messages Reshat, You have some points, however just as there is the possibility of switching RDBMS, there is a possibility that the frontend language will change as well. Furthermore, a greater possibility, I think, that multiple frontends under different platforms will access the one data source. Data integrity constraints MUST be in the database itself anyway (if not, then I suggest using MySQL...), otherwise you are hoping that multiple ADs are going to properly maintain integrity in the applications. IMHO, what Joel suggests is quite reasonable. Rod
Reshat, > I certainly find it a must to use such features as referential > integrity > (even it costs a little), transaction support, custom objects, etc. > which make postgreSQL stand apart from mySQL, etc. Such standard > features as value checks, which I believe can be ported quite easily > also don't arouse my doubts. > I guess features that don't port well across DBs are stored > procedures > and consequently triggers. Those are the things I am referring to. > For > example, if we need to increment a field by a certain value once in > every 2 weeks, would you suggest using stored procedures to > accomplish > it, or app logic? And in general, when would you say it's worth to > use > stored procedures? As Joel points out, you are looking at "portability" from the perspective of an interface programmer. Or, to explicate: THE INTERFACE PROGRAMMER: "I put as much program logic as possible into the interface and middleware code, limiting my database calls to the most strict and limited set of SQL92. That way, my application is PORTABLE to any SQL RDMBS." THE DATABASE PROGRAMMER: "95% of my program's business logic, including custom error messages and input validation, are in the database and attached procedures. This makes my application design PORTABLE to any front-end technology." Both points of view are equally valid ... it just depends on who's making the portability assessment. People tend to regard their area of expertise as indispensable, and other technology as replacable. Ideally, it should depend on the needs assessment of the application being developed, but people are seldom that rational. In a more direct answer to your question, Triggers and Procedures are part of the SQL92/99 Specification, and are supported by all but one of the major server database programs in their current version (those being Postgres, Oracle, MS SQL Server, SAP DB, Phoenix, DB2, SyBase and FrontBase, and the sole holdout being MySQL). However, *implementation* of triggers and procedures varies widely between platforms, as the spec leaves a lot of leeway here. Thus, in the abstract, a design relying on triggers and procedures is perfectly portable *in concept*, but the actual code would need to be re-written for each target platform. -Josh Berkus
<quote who="Josh Berkus"> > Thus, in the abstract, a design relying on triggers and procedures is > perfectly portable *in concept*, but the actual code would need to be > re-written for each target platform. Not only the database code (stored procedures and triggers) needs to be rewritten, but also application (or front-end) calls to stored procedures often need to be rewritten as each database has its way of calling them. a.
<quote who="Josh Berkus"> > Thus, in the abstract, a design relying on triggers and procedures is > perfectly portable *in concept*, but the actual code would need to be > re-written for each target platform. Not only the database code (stored procedures and triggers) needs to be rewritten, but also application (or front-end) calls to stored procedures often need to be rewritten as each database has its way of calling them. a. It appears that there is a real need to standardize at least the interface to procedure calls, so that at least the apps don't have to be re-written. That way, just as mainstream SQL looks alike but is implemented differently, stored procedures would be too. Eventually, perhaps the stored procedures themselves could have standard interface, so that most of them (at least trivial ones) can be ported w/o re-writing. OK, enough daydreaming... :) r.