Column defaults fail with rules on view

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

Responses

Browse pgsql-general by date

  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