Column defaults fail with rules on view

Поиск
Список
Период
Сортировка
От
Тема Column defaults fail with rules on view
Дата
Msg-id 64866.66.212.203.144.1063958413.squirrel@$HOSTNAME
обсуждение исходный текст
Ответы Re: Column defaults fail with rules on view  (Richard Huxton <dev@archonet.com>)
Список 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




В списке pgsql-general по дате отправления:

Предыдущее
От: Oleg Bartunov
Дата:
Сообщение: Re: PostgreSQL versus MySQL
Следующее
От: Rory Campbell-Lange
Дата:
Сообщение: Outer Join help please