From: Mark Gibson <gibsonm@cromwell.co.uk>
To: pgsql-sql@postgresql.org
Subject: Implementation of a bag pattern using rules
Date: Mon, 09 Feb 2004 12:42:10 +0000
Message-ID: <40278022.5070408@cromwell.co.uk> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gibsonm@cromwell.co.uk
Subject: Re: Implementation of a bag pattern using rules
In-Reply-To: <40278022.5070408@cromwell.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox