Message-Id: <7.0.1.0.2.20061213180244.0844cff0@pandimensional.org>
Date: Wed, 13 Dec 2006 18:04:47 -0800
To: pgsql-sql@postgresql.org
From: William Scott Jordan <wsjordan@brownpapertickets.com>
Subject: Rule for multiple entries
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

Hi all!

I have a rule in place that is supposed to adjust a value in one 
table based on how many rows are added or deleted to another table, 
but I'm not getting the results that I hoped for.  If a single sql 
statement adds or deletes multiple entries, the rule still only 
adjusts the value by one, when I want it to adjust the value by the 
number of rows that were added or deleted.

To recreate this problem, first set up the schema:

CREATE TABLE counter (
counter_key int,
counter_value int
) ;

INSERT INTO counter (counter_key, counter_value) VALUES (1, 0) ;

CREATE TABLE entries (
entry_key int
) ;

CREATE RULE entries_insert AS ON INSERT TO entries DO UPDATE counter 
SET counter_value = counter_value + 1 WHERE counter_key = 1 ;
CREATE RULE entries_delete AS ON DELETE TO entries DO UPDATE counter 
SET counter_value = counter_value - 1 WHERE counter_key = 1 ;

Then enter some values:

INSERT INTO entries (entry_key) VALUES (1) ;
INSERT INTO entries (entry_key) VALUES (2) ;
INSERT INTO entries (entry_key) VALUES (3) ;
INSERT INTO entries (entry_key) VALUES (4) ;
INSERT INTO entries (entry_key) VALUES (5) ;

At this point the counter table should show a counter_value of 5, 
because there are 5 entries in the entries table.  Now, if you delete 
3 of those entries, the counter table should show a value of 2, but 
instead it only counts it as a single transaction.

DELETE FROM entries WHERE entry_key > 2 ;

Why doesn't the entries_delete rule hit for each row that's being 
adjusted.  Or, more importantly, how can I adjust the rule so that it 
will count each row that's be added/deleted?

Any suggestions would be appreciated.

-Scott