From: | <btober(at)seaworthysys(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Column defaults fail with rules on view |
Date: | 2003-09-19 08:00:13 |
Message-ID: | 64866.66.212.203.144.1063958413.squirrel@$HOSTNAME |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | 博 翟 | 2003-09-19 08:18:35 | about the pstate node |
Previous Message | Oleg Bartunov | 2003-09-19 07:49:37 | Re: PostgreSQL versus MySQL |