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

Responses

• Re: 'on insert' rules and defaults at 2000-04-06 20:35:00 from Tom Lane

pgsql-hackers by date

Next:	From: Tom Lane	Date: 2000-04-06 20:35:00
	Subject: Re: 'on insert' rules and defaults
Previous:	From: Lamar Owen	Date: 2000-04-06 19:14:01
	Subject: Re: pg_dumplo, thanks :) (fwd)