From: | "Mark Hollomon" <mhh(at)nortelnetworks(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | 'on insert' rules and defaults |
Date: | 2000-04-06 19:33:15 |
Message-ID: | 38ECE67B.A57977AB@americasm01.nt.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Playing around with rules and views, I noticed the
following:
CREATE TABLE t (
i INTEGER,
b BOOLEAN DEFAULT false
);
CREATE VIEW v AS
SELECT * FROM t;
CREATE RULE v_insert AS
ON INSERT TO v DO INSTEAD
INSERT INTO t values ( NEW.i, NEW.b);
mhh=# insert into v values ( 1 );
INSERT 50199 1
mhh=# select * from v;
i | b
---+---
1 |
(1 row)
In other words, the default is not honored. Is there a way to
write the rule so that default on 'b' is honored?
I found the following to work. But the combinatorial explosion
for multiple fields is a killer.
CREATE RULE v_insert_null AS
ON INSERT TO v WHERE NEW.b IS NULL DO INSTEAD
INSERT INTO t values (NEW.i);
CREATE RULE v_insert_not_null AS
ON INSERT TO v WHERE NEW.b IS NOT NULL DO INSTEAD
INSERT INTO t values (NEW.i, NEW.b);
I also thought about COALESCE:
CREATE RULE v_insert AS
ON INSERT TO v DO INSTEAD
INSERT INTO t values (NEW.i, COALESCE(NEW.b, false));
But then two places have to know about the default value.
Any other suggestions?
--
Mark Hollomon
mhh(at)nortelnetworks(dot)com
ESN 451-9008 (302)454-9008
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-04-06 20:35:00 | Re: 'on insert' rules and defaults |
Previous Message | Lamar Owen | 2000-04-06 19:14:01 | Re: pg_dumplo, thanks :) (fwd) |