May be this is not a full explanation, but at least a recepiet, that
works for me:
CREATE TABLE testa (x1 text, x2 text);
CREATE VIEW testb AS SELECT * from testa;
CREATE RULE r0 AS ON INSERT TO testb DO INSTEAD INSERT INTO testa
(x1,x2) VALUES (new.x1,new.x2);
CREATE RULE r1 AS ON INSERT TO testb WHERE new.x1 = 'house' DO INSTEAD
INSERT INTO testa (x1,x2) VALUES (new.x1, 'mouse');
BTW: some time ago I tried the rule system at the TABLEs themselves, and
I couldn't figure out how to write correct statements. With VIEWs it all
work just fine.
Rule #1: RULES for VIEWS :)
-R
On Wed, 2006-06-07 at 13:23 +0200, peter pilsl wrote:
> I dont succeed with writing my rules proper.
>
> I need the following rules:
>
> when I INSERT/UPDATE to a table and a certain condition is true then a
> special field in this data-row should be updated to.
>
>
> I came as far:
>
> # \d testa
> Table "public.testa"
> Column | Type | Modifiers
> --------+------+-----------
> x1 | text |
> x2 | text |
> Rules:
> r1 AS
> ON INSERT TO testa
> WHERE new.x1 = 'house'::text DO UPDATE testa SET x2 = 'mouse'::text
>
>
> this works fine. When I insert a row with x1=house then x2 gets set to
> mouse.
>
> BUT:
>
> i) it always updates all rows in the tables instead of only the current
> row. I guess this is a huge performance-lack on big tables :) I tried
> to get a where oid=new.oid in or something like that, but it did not work.
>
> ii) the above rule does not work on UPDATE, cause I get a deep
> recursion. Each update causes another update on the same table which
> makes postgres break with a nested.loop -error (happily postgres detects
> the recursion :)
>
> thnx for any help
> peter
>
>
>
>
--
-R