From: | Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> |
---|---|
To: | peter pilsl <pilsl(at)goldfisch(dot)at> |
Cc: | PostgreSQL List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: help with rules please |
Date: | 2006-06-07 13:12:23 |
Message-ID: | 1149685944.22835.149.camel@model.home.waw.pl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2006-06-07 13:15:44 | Re: Import Data from MS SQL Server |
Previous Message | Tim Hart | 2006-06-07 13:12:10 | What are the characteristics of a good user-defined data type? |