Re: Default values, inserts, and rules...

On Tue, 20 Aug 2002 22:37:46 -0700
Sean Chittenden <sean(at)chittenden(dot)org> wrote:

> CREATE RULE t_ins AS ON INSERT TO t DO
> INSERT INTO t_log (s,c1,c2) VALUES (CURRVAL('t_s_seq'),NEW.c1,NEW.c2);
> INSERT INTO t (c2) VALUES ('a');
> ERROR: ExecAppend: Fail to add null value in not null attribute c1
>
> I think this was brought up a while ago... Tom, in response to your
> question, moving this into the parser I think would solve the above
> behaviors that I'd classify as incorrect. Am I wrong or missing some
> way of getting the desired behavior?
>

If NEW.c1 doesn't contain the value, to use COALESCE() can avoid
raising that error. And the default value: 42 is correctly inserted into t_log.

CREATE RULE t_ins AS ON INSERT TO t DO
INSERT INTO t_log (s,c1,c2) VALUES (CURRVAL('t_s_seq'),
COALESCE(NEW.c1, 42),
NEW.c2);

Regards,
Masaru Sugawara