Re: 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 ===