> Joel,
>
> > Would it be considered good practice to use functions to generate
> > custom error messages ?
>
> Depends on what you mean by "good practice". The "best practice",
> arguably, would be to do this in some kind of middleware. If,
> however, your project is too lightweight to have middleware, then using
> functions to do so can be pretty good.
>
> An alternate, perhaps much simpler approach, would be to intercept the
> database error messages and transform them into custom error messages
> through text parsing or an error lookup table.
>
There is one drawback to this, the way Postgres raises the exception. Even
if a particular query would generate multiple exceptions, only one is
returned. This could create a situation that would annoy users... they
enter data, are given an error, fix that and then a different error appears.
It would be far superior to return all errors at once.
> > 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.
>
> Absolutely. In fact, if you take this approach, you might drop the
> table constraints as redundant -- provided that you manage your
> checking functions well.
>
This is true and especially important on heavily loaded servers. I would
recommend, however, that one only remove the table/row constraints AFTER
debugging to help expose any bugs in your own functions.
> > 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.
>
> Absolutely. The drawbacks to this approach, however, are:
> 1) managing all these testing triggers
> 2) performance loss if the triggers are significantly slower than
> constraints.
>
This is true, however there are the benefits of:
1) NOT managing constraints in (possibly) multiple application in an attempt
to generate meaningful errors for users
2) performance increase in NOT performing those constraint checks in the
applications (granted probably not enough to offset the overhead of trigger
procedures but should be mentioned in fairness).
