Removed cc to pgsql-patches since that's not the list for this.
John Hansen wrote:
It seems rules don't work as expected. I could be wrong,... In which case, what am I doing wrong?
A rule is like a macro, rewriting the query plan. You're trying to use it as though it is a trigger. The side-effects of rules can be quite subtle and catches most of us out at least once.
Clearly, the first insert below should not update the table as well.
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[]);
The NEW.a doesn't refer to a variable as such, it refers to the updated/inserted value of an actual row in "test". Does that clarify?
In your particular usage you'd want to consider concurrency and locking issues too.
Repost your question on the general/sql lists if you'd like some discussion. It's probably worth checking the list archives too - plenty in there about rule/trigger differences.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
