To: Mark Gibson <gibsonm@cromwell.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Implementation of a bag pattern using rules 
In-reply-to: <40278022.5070408@cromwell.co.uk> 
References: <40278022.5070408@cromwell.co.uk>
Comments: In-reply-to Mark Gibson <gibsonm@cromwell.co.uk>
	message dated "Mon, 09 Feb 2004 12:42:10 +0000"
Date: Mon, 09 Feb 2004 11:09:34 -0500
Message-ID: <20111.1076342974@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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