PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.96
Given the following SQL:
CREATE TABLE "tbl_test" (
"id" int4 NOT NULL,
"b_disabled" bool DEFAULT '0' NOT NULL,
"s_desc" text NOT NULL,
"dt_edited" timestamp (0) without time zone DEFAULT 'now' NOT NULL,
CONSTRAINT "tbl_test_pkey" PRIMARY KEY ("id"),
CONSTRAINT "tbl_test_desc_key" UNIQUE ("s_desc")
) WITHOUT OIDS;
GRANT ALL ON "tbl_test" TO PUBLIC;
CREATE VIEW vu_tbl_test AS SELECT id, s_desc, b_disabled, dt_edited FROM tb=
l_test;
GRANT SELECT, INSERT, UPDATE ON TABLE vu_tbl_test TO GROUP PUBLIC;
CREATE RULE rul_vu_test_i1 AS ON INSERT TO vu_tbl_test DO INSTEAD (INSERT I=
NTO tbl_test (id, s_desc, b_disabled) VALUES(NEW.id, NEW.s_desc, NEW.b_disa=
bled));
CREATE RULE rul_vu_tbl_test_u1 AS ON UPDATE TO vu_tbl_test DO INSTEAD (UPDA=
TE tbl_test SET s_desc=3DNEW.s_desc, b_disabled=3DNEW.b_disabled WHERE (id =
=3D NEW.id));
I have a major problem with DEAFAULT values:
For example executing the following SQL:
Bugs=3D> INSERT INTO tbl_test (id, s_desc) VALUES('1', 'Std insert test');
INSERT 0 1
This works ok but the equivalent for the view does not:
Bugs=3D> INSERT INTO vu_tbl_test (id, s_desc) VALUES('2', 'View insert test=
');
ERROR: ExecInsert: Fail to add null value in not null attribute b_disabled
A further investigation:
Bugs=3D> INSERT INTO tbl_test (id, s_desc, b_disabled) VALUES('3', 'Null in=
sert test', NULL);
ERROR: ExecInsert: Fail to add null value in not null attribute b_disabled
shows that default values are not being set when NULL values are specified =
in an INSERT statement.
This might be ok for straight tables, as the client software can easily avo=
id INSERTS with NULLs, but this situation is totally unusable for views bec=
ause you don't know whether the user was supplying a NULL or whether it sim=
ply wasn't specified. Even if did want to avoid this NULL situation the num=
ber of permutations for the INSERT statements would escalate to the ridicul=
ous when you have anything beyond 2 such columns.
Any thoughts or work-arounds for views would be welcomed.
Regards
Donald Fraser.
