From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Gibson <gibsonm@cromwell.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Implementation of a bag pattern using rules
Date: Mon, 09 Feb 2004 11:09:34 -0500
Message-ID: <20111.1076342974@sss.pgh.pa.us> (raw)
In-Reply-To: <40278022.5070408@cromwell.co.uk>
References: <40278022.5070408@cromwell.co.uk>
Mark Gibson <gibsonm@cromwell.co.uk> writes:
> 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;
This can't work because an ON INSERT rule fires after the INSERT itself
is executed. You have the equivalent of
INSERT INTO ... WHERE NOT EXISTS(SELECT ...);
UPDATE ... WHERE item = NEW.item AND EXISTS(SELECT ...);
The INSERT will execute because there's no row matching the EXISTS(),
and then the UPDATE will execute too because now there is a matching
row. In some contexts this is a feature. However, you want a single
test to control both actions.
I think you need to use a BEFORE INSERT trigger instead. It could
do something like
-- see if there is an existing row, if so update it
UPDATE bag_test SET qty = qty + NEW.qty WHERE item = NEW.item;
-- if there was one, suppress the INSERT
IF found THEN
RETURN NULL;
END IF;
-- else allow the INSERT
RETURN NEW;
You could also extend the trigger to handle the
delete-upon-reaching-zero logic.
regards, tom lane
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: tgl@sss.pgh.pa.us, gibsonm@cromwell.co.uk
Subject: Re: Implementation of a bag pattern using rules
In-Reply-To: <20111.1076342974@sss.pgh.pa.us>
* 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