X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 79112D1C9E1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  9 Feb 2004 13:48:36 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 32890-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  9 Feb 2004 09:48:38 -0400 (AST)
Received: from crommail.cromwell-tools.co.uk
	(host217-40-164-180.in-addr.btopenworld.com [217.40.164.180])
	by svr1.postgresql.org (Postfix) with ESMTP id DD7E2D1D8AE
	for <pgsql-sql@postgresql.org>; Mon,  9 Feb 2004 09:48:27 -0400 (AST)
Message-ID: <40278FDD.6080301@cromwell.co.uk>
Date: Mon, 09 Feb 2004 13:49:17 +0000
From: Mark Gibson <gibsonm@cromwell.co.uk>
MIME-Version: 1.0
To: Robert Creager <Robert_Creager@LogicalChaos.org>
Cc: PGSQL <pgsql-sql@postgresql.org>
Subject: Re: Implementation of a bag pattern using rules
References: <20040209060207.3d208b4e.Robert_Creager@LogicalChaos.org>
In-Reply-To: <20040209060207.3d208b4e.Robert_Creager@LogicalChaos.org>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/79
X-Sequence-Number: 16770

Robert Creager wrote:

>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?
>
>  
>
I probably didn't make this clear enough:

The system doesn't choose, YOU choose EITHER 'bag_abs' OR 'bag_rel' 
depending
on which behaviour is most appropriate for your application.
'bag_del' can be used in combination with either, to remove empty items.

The 'bag_abs'/'bag_del' rules work perfectly - I've provided them for 
feedback,
and hopefully others will find them useful.

It's only the 'bag_rel' rule that is giving me a headache.

Also, IIRC, rules are applied in alphabetical order, NOT the order in 
which they were created.

Cheers.

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