From: Mark Gibson <gibsonm@cromwell.co.uk>
To: Robert Creager <Robert_Creager@LogicalChaos.org>
Cc: PGSQL <pgsql-sql@postgresql.org>
Subject: Re: Implementation of a bag pattern using rules
Date: Mon, 09 Feb 2004 13:49:17 +0000
Message-ID: <40278FDD.6080301@cromwell.co.uk> (raw)
In-Reply-To: <20040209060207.3d208b4e.Robert_Creager@LogicalChaos.org>
References: <20040209060207.3d208b4e.Robert_Creager@LogicalChaos.org>
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.
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, Robert_Creager@LogicalChaos.org
Subject: Re: Implementation of a bag pattern using rules
In-Reply-To: <40278FDD.6080301@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