RULES doesn't work as expected

From: "John Hansen" <john(at)geeknet(dot)com(dot)au>
To: <pgsql-bugs(at)postgresql(dot)org>
Cc: <pgsql-patches(at)postgresql(dot)org>
Subject: RULES doesn't work as expected
Date: 2005-01-12 07:44:22
Message-ID: 5066E5A966339E42AA04BA10BA706AE5622B@rodrick.geeknet.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-patches

It seems rules don't work as expected.
I could be wrong,... In which case, what am I doing wrong?

Clearly, the first insert below should not update the table as well.

... John

CREATE TABLE test (a text, b int4[]);

CREATE RULE test_rule AS
ON INSERT TO test
WHERE exists(SELECT 1 FROM test WHERE a = NEW.a)
DO INSTEAD
UPDATE test SET b = b + NEW.b WHERE a = NEW.a;

db1=# INSERT INTO test (a,b) VALUES (1,'{1}'::int4[]);
INSERT 0 1
db1=# SELECT * FROM test;
a | b
---+-------
1 | {1,1}
(1 row)

db1=# INSERT INTO test (a,b) VALUES (1,'{1}'::int4[]);
INSERT 0 0
db1=# SELECT * FROM test;
a | b
---+---------
1 | {1,1,1}
(1 row)

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andrew Kompaneev 2005-01-12 07:57:25 BUG #1387: Sort order don't work correctly
Previous Message Gustav Lindenberg 2005-01-12 07:43:58 BUG #1386: 2 x 3.1 Gz Intel Cpu, 4Gb RAM, 2Gb Swap, Linux lssprod.localdomain 2.4.21-20.ELsmp #1 SMP Wed Aug 18

Browse pgsql-patches by date

  From Date Subject
Next Message Simon Riggs 2005-01-12 08:23:57 SUSE port (was [ANNOUNCE] PostgreSQL 8.0.0 Release Candidate 5)
Previous Message Brendan Jurd 2005-01-12 06:34:43 Re: Increased error verbosity when querying row-returning