help with rules please

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