[SQL] Help converting Oracle instead of triggers to PostgreSQL

[Clint Stotesbery]

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 it is 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 of a 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match