Re: Column defaults fail with rules on view

On Friday 19 September 2003 13:36, btober(at)seaworthysys(dot)com wrote:
> > 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]
> >
> > Hmm - well, you're explicitly telling it to insert VALUES (...,
> > new.field3, ...) so if new.field3 is null then it *should* do that.
>
> I (apparently mistakenly) thought that the point of specifying DEFAULT
> values in the table column definition was so that the default value would
> be inserted automatically rather than a null. And anyway, that IS how it
> seems to work when I do the insert for row A to the table directly with
>
> INSERT INTO test_table VALUES ('A');
>
> where fields 2, 3, and 4 have not been assigned values. Why do they get
> the default in this case?

Because you're not doing
INSERT INTO test_table VALUES ('A',null,null)

> > 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.
>
> My work-around has been to define BEFORE INSERT triggers with lines like
>
> SELECT INTO new.field3 COALESCE(new.field3, 1);
>
> testing for and optionally assigning the default, but I really don't like
> having to explicitly do that for every table and NOT NULL column, since I
> make pretty much routine use of RULES on VIEWS to make writeable views
> the interface to my user application.
>
> > Out of curiosity, can you tell me what happens if you insert into the
> > view ('C',DEFAULT,DEFAULT)?
> > Richard Huxton
>
> Same script, but with
>
>
> INSERT INTO test_table VALUES ('A');
> INSERT INTO test_table_v VALUES ('B');
> INSERT INTO test_table_v VALUES ('C', DEFAULT, DEFAULT);
>
> gives
>
> field1 | field2 | field3 | field4
> --------+--------+--------+-----------------
> A | 1 | 1 | (default value)
> B | 2 | |
> C | 3 | |
> (3 rows)
>
> -- so no change in behavior. I notice that field2, which was declared
> type SERIAL, and so also has a DEFAULT, but one which calls the nextval
> function rather than simply assigning a value, gets its default value
> assigned in both the table insert and the view insert.

Because you don't specify field2 in your RULE.
--
Richard Huxton
Archonet Ltd