Re: Column defaults fail with rules on view

On Friday 19 September 2003 09:00, btober(at)seaworthysys(dot)com wrote:
> I'm finding that column defaults are not being assigned to nulls when I
> do an insert by way of a an ON INSERT rule on a view. For example, the
> following script
[snip]
> CREATE RULE test_table_ri AS ON INSERT TO test_table_v DO INSTEAD
> INSERT INTO test_table (field1, field3, field4)
> VALUES (new.field1, new.field3, new.field4);
[snip]
> Is this supposed to work that way? I would expect field3 and field4 to
> have their respective column defaults assigned on the second INSERT (row
> B), just like on the first INSERT (row A).

Hmm - well, you're explicitly telling it to insert VALUES (..., new.field3,
...) so if new.field3 is null then it *should* do that.

Now - how you should go about getting the default I don't know. You could
build a rule with WHERE NEW.field3 IS NULL and then not pass field3, but that
would stop you explicitly setting it to null.

Out of curiosity, can you tell me what happens if you insert into the view
('C',DEFAULT,DEFAULT)?
--
Richard Huxton
Archonet Ltd