Thread

1. 'on insert' rules and defaults

   Mark Hollomon <mhh@nortelnetworks.com> — 2000-04-06T19:33:15Z

   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@nortelnetworks.com
   ESN 451-9008 (302)454-9008
   
   

2. Re: 'on insert' rules and defaults

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-04-06T20:35:00Z

   "Mark Hollomon" <mhh@nortelnetworks.com> writes:
   > In other words, the default is not honored.
   
   Right, since the INSERT written in the rule provides an explicit
   specification of what should be inserted into t.  NEW.b is NULL
   and that's what gets inserted.
   
   > 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.
   
   Another problem with that is that there's no way to specify insertion
   of a NULL into b.
   
   > Any other suggestions?
   
   You really want default substitution to be done by the parser.
   Any later is too late because you won't be able to tell an explicit
   NULL from a defaulted column.
   
   I haven't tried it, but I think it would work to declare the "view"
   as a real table and then attach the rules to it:
   
   CREATE TABLE t ( 
   	i INTEGER,
   	b BOOLEAN DEFAULT false
   );
   
   CREATE TABLE v ( 
   	i INTEGER,
   	b BOOLEAN DEFAULT false
   );
   
   CREATE RULE _RETv AS
   	ON SELECT TO v DO INSTEAD
   	SELECT * FROM t;
   
   CREATE RULE v_insert AS
   	ON INSERT TO v DO INSTEAD
   	INSERT INTO t values ( NEW.i, NEW.b);
   
   Then when you do
   
   INSERT INTO v VALUES(43);
   
   the default defined for v.b gets applied by the parser, before the
   rule substitution happens.
   
   This still means you have two places that know the default, but
   since they're both table declarations maybe it's not so bad.
   
   			regards, tom lane