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
\set ON_ERROR_STOP ON
\c template1
--DROP DATABASE testdb;
CREATE DATABASE testdb;
\c testdb
create table test_table (
field1 char(1) not null,
field2 serial,
field3 integer default 1,
field4 varchar(24) default '(default value)',
constraint testdb_pkey primary key (field2));
INSERT INTO test_table VALUES ('A');
SELECT * FROM test_table;
CREATE VIEW test_table_v AS
SELECT field1, field3, field4 FROM test_table;
SELECT * FROM test_table;
CREATE RULE test_table_rd AS ON DELETE TO test_table_v DO INSTEAD
DELETE FROM test_table WHERE field1 = old.field1;
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);
CREATE RULE test_table_ru AS ON UPDATE TO test_table_v DO INSTEAD
UPDATE test_table SET
field1 = new.field1,
field3 = new.field3,
field4 = new.field4
WHERE field1 = old.field1;
INSERT INTO test_table_v VALUES ('B');
SELECT * FROM test_table;
-- produces this output
CREATE DATABASE
CREATE TABLE
INSERT 147461 1
field1 | field2 | field3 | field4
--------+--------+--------+-----------------
A | 1 | 1 | (default value)
(1 row)
-- above works fine, but then
CREATE VIEW
field1 | field2 | field3 | field4
--------+--------+--------+-----------------
A | 1 | 1 | (default value)
(1 row)
CREATE RULE
CREATE RULE
CREATE RULE
INSERT 147468 1
field1 | field2 | field3 | field4
--------+--------+--------+-----------------
A | 1 | 1 | (default value)
B | 2 | |
(2 rows)
-- notice how field3 and field4 are not assigned their defaults for row B!
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).
wassup wit dat?
~Berend Tober
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
