Thread: Rules with "Where" Referencing Other Tables

I got away with this under 7.2.3.  I didn't read the docs.  However, under 7.3.1, it blew up the backend.  Since there
isobviously a good reason for this prohibition, why does the system allow creation of rules that reference other tables
inthe where clause? 

Here is a very simple case.  It doesn't blow anything up.  My more complex case did, when I did repeated inserts within
atransaction.  I don't want to spend a lot of time running it to ground since I should have read the docs and not even
triedit! 

create table test (
        testid int,
        testdata varchar);

create table test2 (
        test2id int,
        test2data varchar);

insert into test2 values (1, 'blah');

create or replace view testview as
select * From test;

create or replace rule testrule as on insert to test where
(select test2data from test2 where test2id = 1) = 'blah'
do instead nothing;

insert into test values (2, 'foobar');

update test2 set test2data = 'asdkljf';

insert into test values (2, 'foobar');


Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
(253) 798-3549

"If any man can convince me and bring home to me that I do not think or act aright, gladly will I change; for I search
aftertruth, by which man never yet was harmed. But he is harmed who abideth on still in his deception and ignorance" 

              -- Marcus Aurelius

"Ian Harding" <ianh@tpchd.org> writes:
> I got away with this under 7.2.3.  I didn't read the docs.  However, under 7.3.1, it blew up the backend.  Since
thereis obviously a good reason for this prohibition, why does the system allow creation of rules that reference other
tablesin the where clause? 
> Here is a very simple case.  It doesn't blow anything up.

Would you show us a case where it *does* blow up?

I'd prefer not to forbid subselects entirely in rule WHERE clauses,
but if we have to ...

            regards, tom lane