Mark Gibson <[EMAIL PROTECTED]> 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.
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
