Обсуждение: help with rules please
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 -- mag. peter pilsl goldfisch.at IT- & dataconsulting tel: +43 650 3574035 tel: +43 1 8900602 fax: +43 1 8900602 15 pilsl@goldfisch.at
am 07.06.2006, um 13:23:09 +0200 mailte peter pilsl folgendes: > > 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. My suggestion: write a TRIGGER for this. Examples for TRIGGER: http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
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