From: Tom Lane <tgl@sss.pgh.pa.us>
To: Joel Burton <jburton@scw.org>
Cc: pgsql-bugs@postgresql.org
Cc: pgsql-docs@postgresql.org
Subject: Re: Rules with Conditions: Still Doesn't Work (Bug Rpt)
Date: Tue, 05 Dec 2000 15:42:54 -0500
Message-ID: <23518.976048974@sss.pgh.pa.us> (raw)
In-Reply-To: <3A2D092D.14054.15DCFC7@localhost>
References: <3A2B7D2B.27409.3A1124@localhost>
<3A2D092D.14054.15DCFC7@localhost>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: tgl@sss.pgh.pa.us, jburton@scw.org, pgsql-bugs@postgresql.org
Subject: Re: Rules with Conditions: Still Doesn't Work (Bug Rpt)
In-Reply-To: <23518.976048974@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox