Re: Implementation of a bag pattern using rules

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 '
DECLAREoldqty bag_test.qty%TYPE;
BEGINIF 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
SETqty = 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.