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 45D2DD1C4BC
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  9 Feb 2004 17:23:17 +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 30245-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  9 Feb 2004 13:23:10 -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 B7857D1D55F
	for <pgsql-sql@postgresql.org>; Mon,  9 Feb 2004 13:23:08 -0400 (AST)
Message-ID: <4027C235.4050600@cromwell.co.uk>
Date: Mon, 09 Feb 2004 17:24:05 +0000
From: Mark Gibson <gibsonm@cromwell.co.uk>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Implementation of a bag pattern using rules
References: <40278022.5070408@cromwell.co.uk> <20111.1076342974@sss.pgh.pa.us>
In-Reply-To: <20111.1076342974@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/90
X-Sequence-Number: 16781

Tom Lane wrote:

>Mark Gibson <gibsonm@cromwell.co.uk> writes:
>  
>
>>    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.
>  
>

I suspected that it may be impossible with rules, but I thought I'd ask,
I'm still trying to get to grips with them.

>I think you need to use a BEFORE INSERT trigger instead.
>You could also extend the trigger to handle the
>delete-upon-reaching-zero logic.
>  
>

So, here's my proof-of-concept trigger for the relative quantities:

CREATE OR REPLACE FUNCTION bag_rel_trigger() RETURNS TRIGGER AS '
DECLARE
 oldqty bag_test.qty%TYPE;
BEGIN
 IF NEW.qty <> 0 THEN
  SELECT INTO oldqty qty FROM bag_test WHERE item = NEW.item;
  IF NOT FOUND AND NEW.qty > 0 THEN
   RETURN NEW;
  END IF;
  IF oldqty + NEW.qty <= 0 THEN
   DELETE FROM bag_test WHERE item = NEW.item;
  ELSE
   UPDATE bag_test SET qty = qty + NEW.qty WHERE item = NEW.item;
  END IF;
 END IF;
 RETURN NULL;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER bag_rel BEFORE INSERT ON bag_test
FOR EACH ROW EXECUTE PROCEDURE bag_rel_trigger();

I think it should be possible to make the trigger generic for any table,
the quantity column could be passed as a parameter to the trigger,
but it would require some horribly complex code to determine
the primary key and lots of EXECUTE calls -
a lot of overhead each time the trigger is called :(
I was thinking maybe of a function thats create a trigger optimized for 
the table.
Any ideas?

Cheers

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