Re: Multiple Rules :: Postgres Is confused !!

Najib Abi Fadel wrote:
>>>
>>>AS YOU CAN SEE TWO ROWS WHERE UPDATED INSTEAD OF ONE !!
>>>THE COMMON THINGS BETWEEN THE TWO ROWS  IS THAT THE FIELDS: cod_etu,
>>>cursus_id,vers_id,code_type_academic  are the same
>>
>>Because that's what you asked upd1/2 to do for you. To see what is
>>happening, try selecting row id=53597 then manually running each rule
>>yourself, substituting in the OLD.foo from your selected row. You should
>>find that there are two rows that match 53597 on (cursus_id, vers_id,
>>traiter, code_type_academic) - itself and one other.
>
>
> Sorry, I didn't understand the manuel test procedure
>
> What is happening here? I am doing an update and the condition is on the ID
> and it is corresponding to the last Rule so why should the other rules
> interfer.

ALL rules get executed. Conditions get combined (actually, parse trees
get merged).

=== BEGIN rule_test.sql ===
CREATE TABLE foo (a int4 PRIMARY KEY, b text);

COPY foo FROM stdin;
1    aaa
2    bbb
3    ccc
4    aaa
5    bbb
6    ccc
\.

CREATE VIEW foo_v AS SELECT * FROM foo WHERE b='bbb';

CREATE RULE foo_v_upd1 AS ON UPDATE TO foo_v DO INSTEAD
UPDATE foo SET b = 'z' || NEW.b WHERE a = OLD.a;

SELECT * FROM foo ORDER BY a;

UPDATE foo_v SET b='xxx';

SELECT * FROM foo ORDER BY a;
=== END rule_test.sql ===

This will update 2 rows (those with b='bbb') since we impose no WHERE in
our update but the view does. The OLD/NEW refer to target rows
before/after the change.

Does that make things clearer?
--
   Richard Huxton
   Archonet Ltd