Re: Problem with triggers - Mailing list pgsql-general

From Tom Lane
Subject Re: Problem with triggers
Date
Msg-id 7062.1276699984@sss.pgh.pa.us
Whole thread Raw
In response to Re: Problem with triggers  (Adrian von Bidder <avbidder@fortytwo.ch>)
List pgsql-general
Adrian von Bidder <avbidder@fortytwo.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

pgsql-general by date:

Previous
From: Dimitri Fontaine
Date:
Subject: Re: Monitoring activities of PostgreSQL
Next
From: Raymond O'Donnell
Date:
Subject: Re: hi,i write a function in postgresql source code, how to register this function?