Re: Rule system and unsucessful updates.

Tom Lane wrote:
> Matthew Schumacher <matt(dot)s(at)aptalaska(dot)net> writes:
>
>>I'm having trouble getting the rule system to work on updates that do
>>not match the where clause.
>
>
> You did not say what you mean by "doesn't work", but what I suspect you
> are getting bit by is that ON UPDATE rules fire before the original
> query is done. By the time the original query executes, you've inserted
> a row into accounting_tab and so its added condition fails.
>
> regards, tom lane

Thanks for your reply Tom, your help is always appreciated...

By doesn't work I am saying that I never get a row inserted. Here is a
complete example of what I'm talking about:

create table test (id int, data varchar(100));

CREATE OR REPLACE RULE
testUpdate
AS ON UPDATE TO
test
WHERE
NOT EXISTS (
SELECT
id
FROM
test
WHERE
id = NEW.id
)
DO INSTEAD
INSERT INTO test (
id,
data
) values (
NEW.id,
'test'
);

update test set id = 1, data = 'test';

As you can see, this is a simple matter of inserting instead of updating
where the key (id) is missing from the table. But the insert never happens.

Your comment about ON UPDATE rules firing before the original query is
desired in this case because the conditions should be met (id doesn't
exist) then the insert query processed. I have to be missing something
here but I just can't see why it won't insert.

If I do the same thing except update when I see an insert query it
correctly updates the rows, but inserts new ones too despite me
declaring DO INSTEAD. Here is an example:

create table test (id int, data varchar(100));

CREATE OR REPLACE RULE
testUpdate
AS ON INSERT TO
test
WHERE
EXISTS (
SELECT
id
FROM
test
WHERE
id = NEW.id
)
DO INSTEAD
UPDATE
test
SET
id = NEW.id,
data = 'test';

insert into test (id, data) values (1, 'test');
insert into test (id, data) values (2, 'test');

Select * from test;

id | data
----+------
2 | test
2 | test

I really don't see why this isn't working as expected either. Wouldn't
DO INSTEAD cause it to omit the original insert query?

Thanks,

schu