To: "Joel Burton" <jburton@scw.org>
cc: pgsql-bugs@postgresql.org, pgsql-docs@postgresql.org
Subject: Re: Rules with Conditions: Still Doesn't Work (Bug Rpt) 
In-reply-to: <3A2D092D.14054.15DCFC7@localhost> 
References: <3A2B7D2B.27409.3A1124@localhost>
	<3A2D092D.14054.15DCFC7@localhost>
Comments: In-reply-to "Joel Burton" <jburton@scw.org>
	message dated "Tue, 05 Dec 2000 15:26:37 -0500"
Date: Tue, 05 Dec 2000 15:42:54 -0500
Message-ID: <23518.976048974@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Joel Burton" <jburton@scw.org> writes:
> Just to be clear (as much for other as for me): this should fix the 
> syntax as my (more recent) bug report submitted? That is, one non-
> conditional rule that tries to handle both inserting and updating 
> together, as opposed to 2 different conditional rules that, together, 
> try to handle all cases.

Right.  The unconditional rule definitely *should* work; you were
just running into bugs in rewriting INSERT...SELECT actions, which
really had nothing to do with whether the rule is conditional or not.
(Had you written INSERT ... VALUES, you'd have seen no problem.)

I'm not convinced yet whether the system should accept conditional
rules to implement views or not.

BTW, the following hack seems to work fine (as of current sources
anyway) if you are really intent on a bunch of conditional rules:
make an unconditional "do instead nothing" plus as many conditional
add-on rules as you want.  For example,

create view foo as select * from int4_tbl;

select * from foo;
     f1
-------------
           0
      123456
     -123456
  2147483647
 -2147483647
(5 rows)

insert into foo values (23);
ERROR:  Cannot insert into a view without an appropriate rule

-- Just to keep the rewriter from complaining:
create rule foo1 as on insert to foo do instead nothing;

create rule foo2 as on insert to foo where new.f1 > 100
do insert into int4_tbl values(new.f1);

create rule foo3 as on insert to foo where new.f1 < 0
do insert into int4_tbl values(- new.f1);

insert into foo values (123);
INSERT 145131 1
insert into foo values (-1000);
INSERT 145132 1
insert into foo values (10);
INSERT 0 0

select * from foo;
     f1
-------------
           0
      123456
     -123456
  2147483647
 -2147483647
         123
        1000
(7 rows)

This also gives you well-defined behavior if none of the conditional
rules fire: nothing happens.

			regards, tom lane