Implementation of a bag pattern using rules - Mailing list pgsql-sql

Hello,   I've been playing around with a simple solution for a bag or sparse 
matrix using rules,
but have encountered a few problems I wish to discuss.
The bag pattern is commonly used for shopping baskets (item => quantity).
This sollution can also be used for a sparse matrix too (row,col => value).

Example:

CREATE TABLE bag_test
( item  text PRIMARY KEY, qty  integer
);

To add/modify/del items in the above table is tedious,
you need to first check for existence of an item then choose your SQL
statement (INSERT/UPDATE/DELETE/do nothing).
I want to be able to add/modify/del an item using only INSERT.

eg:
INSERT INTO bag_test VALUES ('apple', 1);
INSERT INTO bag_test VALUES ('apple', 12);

In the second statement, ee have a choice though, of whether to
increase the quantity of 'apple' by 12, or set the quantity of 'apple' 
to 12.

So, for the absolute option (set 'apple' to 12), we can use the 
following rule:
   CREATE RULE bag_abs AS ON INSERT TO bag_test   WHERE     EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item)   DO
INSTEAD  UPDATE bag_test SET qty = NEW.qty WHERE item = NEW.item;
 

I also want the item to be deleted if it's quantity is <= 0:
   CREATE RULE bag_del AS ON UPDATE TO bag_test   WHERE     NEW.qty <= 0   DO INSTEAD   DELETE FROM bag_test WHERE item
=NEW.item;
 

Alternatively, for the relative option (increase 'apple' by 12), replace 
the 'bag_abs' rule with:
   CREATE RULE bag_rel AS ON INSERT TO bag_test   WHERE     EXISTS (SELECT 1 FROM bag_test WHERE item = NEW.item)   DO
INSTEAD  UPDATE bag_test SET qty = qty + NEW.qty WHERE item = NEW.item;
 

(You still need the 'bag_del' rule if you want quantity <= 0 to be deleted)

Unfortunately there is a problem with 'bag_rel':
When the item already exists, it works fine, the item's quantity
is increased by the amount given in the INSERT statement.
BUT, if the item doesn't exist it gets double the quantity given in the 
statement.
eg:
   > SELECT * FROM bag_test;    item | qty   ------+-----   (0 rows)
   > INSERT INTO bag_test VALUES ('apple', 12);   INSERT 0 1   > SELECT * FROM bag_test;    item  | qty   -------+-----
  apple |  24   (1 row)
 

This is double the expected value!
   > INSERT INTO bag_test VALUES ('apple', 12);   INSERT 0 0   > SELECT * FROM bag_test;    item  | qty   -------+-----
  apple |  36   (1 row)
 

But, this worked fine (increased by 12)!
   > INSERT INTO bag_test VALUES ('apple', -36);   INSERT 0 0   > SELECT * FROM bag_test;    item | qty   ------+-----
(0 rows)
 

Deleting works fine too.

Does anyone know how to prevent the problem with the initial insert?
I've read 'The Rule System' chapter several times, it's fairly heavy going,
and results in much head scratching, but I still can't work out how to 
fix it.

Any suggestions on improving the rules?
Other than the problem mentioned, can anyone see a flaw in this method?

Cheers

-- 
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.