Re: Column defaults fail with rules on view

On Friday 19 September 2003 13:36, btober@seaworthysys.com wrote:
> > On Friday 19 September 2003 09:00, btober@seaworthysys.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