Re: [GENERAL] Column defaults fail with rules on view

[btober]

> On Friday 19 September 2003 09:00, [EMAIL PROTECTED] 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?

>
> 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.

~Berend Tober




---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Column defaults fail with rules on view

[Richard Huxton]

On Friday 19 September 2003 09:00, [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html