Re: [SQL] rewriting values with before trigger

Robert,

> You misunderstood. I don't think it's a bug in postgresql, it's a bug in
the
> application that is hitting against my database. When it doesn't have a
value
> for the timestamp field, it either needs to drop it from the insert statment
> or convert it to null; not send a ''

Incidentally, this sort of problem is why most of my apps are based on "push"
data functions. i.e., instead of the client calling:
INSERT INTO foo VALUES ( id, bar1, bar2, bar3 );
it calls
SELECT df_modify_foo ( id, bar1, bar2, bar3 );

Data-push functions allow me to do a whole array of validation and custom
error message return that would be impractical with triggers. It also
allows me to build security checks in to the back-end, via:

SELECT df_modify_foo ( user_id, session_key, id, bar1, bar2, bar3 )

... allowing me to check all of the following things:
Does the user have a valid session?
Does the user have rights to foo?
Does the user have a lock on foo?
Is this a new foo record, or a modified one?
etc.

--
-Josh Berkus
Aglio Database Solutions
San Francisco