Re: Problem with triggers

Adrian von Bidder <avbidder(at)fortytwo(dot)ch> writes:
> On Wednesday 16 June 2010 00.56:14 Adrian Klaver wrote:
>> The database is beating you to the validation.

> With triggers. A question to the experts: Couldn't this, in theory, be
> implememnted within the rules system?

No, it's pretty much the same problem. If you've got something like
INSERT INTO foo VALUES('bar');
the literal is already going to have been fed to the appropriate input
function long before any rule would be considered.

If you're really desperate to do this type of checking on the database
side, you could consider having your app call a stored procedure instead
of trying to touch the table directly. For example,

CREATE FUNCTION insert_into_foo(text, text, text) ...

SELECT insert_into_foo('bar', 'baz', ...);

The function would then try to cast its input strings to the appropriate
types, and could catch errors and replace them with its own messages.

Mind you, I remain far from convinced that you're going to improve on
the built-in error messages this way.

regards, tom lane