I have some instead of triggers in Oracle, some update instead of triggers
and some insert instead of triggers. I was thinking that I could maybe use
instead of rules in PostgreSQL to get the same effect. I converted the
instead of trigger in Oracle into a PostgreSQL function below:
CREATE OR REPLACE FUNCTION t_vproduct()
RETURNS VOID AS ' DECLARE v_productsetno numeric; v_productno numeric; v_prodqty numeric; v_setqty
numeric; oldqoh numeric; newqoh numeric;
--cursor to to get set number, sub-product_no and their quantities in
the productset prodset_cur CURSOR IS SELECT productset_no, product_no, prod_qty FROM productset WHERE
productset_no= old.product_no;
BEGIN oldqoh := old.qoh; newqoh := new.qoh;
--opening and fetching the cursor in the variables OPEN prodset_cur; FETCH prodset_cur INTO
v_productsetno,v_productno, v_prodqty;
--checking if product is a set or individual --if it is not a set then update product table IF NOT FOUND
THEN UPDATE product SET qoh = qoh - (oldqoh - newqoh) WHERE product_no = old.product_no; --if
itis a SET then ELSIF FOUND THEN v_setqty := (oldqoh - newqoh); --SET quantity
--loop updates each sub products qoh in the set LOOP UPDATE product --multiplying quantity
ofa product in a set
with quantity of productset, to get total quantity of individual product in
a set SET qoh = qoh - (v_prodqty * v_setqty) WHERE product_no = v_productno;
FETCH prodset_cur INTO v_productsetno, v_productno, v_prodqty;
EXIT WHEN NOT FOUND; END LOOP;
CLOSE prodset_cur; END IF;
RETURN; END; ' LANGUAGE 'plpgsql';
Then my guess for the rule is:
CREATE OR REPLACE RULE r_vproduct AS ON UPDATE TO vproduct DO INSTEAD PERFORM t_vproduct();
I know that function isn't going to work the way I have it. In Oracle that
function was defined as a trigger:
CREATE OR REPLACE TRIGGER t_vproduct
INSTEAD OF UPDATE
ON v_product
v_product is a view. Getting access to new and old is going to be at least
one problem I can see. Perhaps I can pass in NEW and OLD into the t_vproduct
function from the rule (DO INSTEAD PERFORM t_vproduct(new, old);). I'm not
sure what to do.
_________________________________________________________________
Take advantage of our best MSN Dial-up offer of the year � six months
@$9.95/month. Sign up now! http://join.msn.com/?page=dept/dialup
