From: Mark Gibson <gibsonm@cromwell.co.uk>
To: pgsql-sql@postgresql.org
Subject: Re: Implementation of a bag pattern using rules
Date: Mon, 09 Feb 2004 17:24:05 +0000
Message-ID: <4027C235.4050600@cromwell.co.uk> (raw)
In-Reply-To: <20111.1076342974@sss.pgh.pa.us>
References: <40278022.5070408@cromwell.co.uk>
<20111.1076342974@sss.pgh.pa.us>
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.
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
Subject: Re: Implementation of a bag pattern using rules
In-Reply-To: <4027C235.4050600@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