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

When grilled further on (Mon, 09 Feb 2004 12:42:10 +0000),
Mark Gibson <gibsonm@cromwell.co.uk> confessed:

> 
>     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;
> 
>     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;

I'm no expert, just up early.  I believe both of these rules are tripping. 
bag_abs is likely going first, then bag_rel, so bag_abs is inserting the record,
then bag_rel is updating it.  You could verify this by deleting the two rules,
then re-creating in the opposite order, and see if your inserted values change. 

How would you expect the system to choose one of the two rules, which is what
you apparently expect?

Later,
Rob

-- 05:57:27 up 16:48,  3 users,  load average: 2.23, 2.17, 2.16