Rules with "Where" Referencing Other Tables

From: "Ian Harding" <ianh(at)tpchd(dot)org>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Rules with "Where" Referencing Other Tables
Date: 2003-02-04 19:45:49
Message-ID: se3fa83b.096@mail.tpchd.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 is obviously a good reason for this prohibition, why does the system allow creation of rules that reference other tables in the 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 a transaction. I don't want to spend a lot of time running it to ground since I should have read the docs and not even tried it!

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(at)tpchd(dot)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 after truth, by which man never yet was harmed. But he is harmed who abideth on still in his deception and ignorance"

-- Marcus Aurelius

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrew Sullivan 2003-02-04 19:46:02 Re: Q: explain on delete
Previous Message Stephan Szabo 2003-02-04 19:42:27 Re: Drop constraint in PostgreSQL 7.1.2