Skip site navigation (1) Skip section navigation (2)

'on insert' rules and defaults

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 (view raw or flat)
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

Responses

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group