using functions to generate custom error messages
От | Joel Rodrigues |
---|---|
Тема | using functions to generate custom error messages |
Дата | |
Msg-id | B260A923-F852-11D6-AE44-0005024EF27F@Phreaker.net обсуждение исходный текст |
Список | pgsql-novice |
[I just sent this using the wrong e-mail address twice (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
В списке pgsql-novice по дате отправления: