Reply-To: cstotesbery@acm.org
From: "Clint Stotesbery" <cstotes@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Help converting Oracle instead of triggers to PostgreSQL
Date: Wed, 03 Dec 2003 19:34:15 +0000
Mime-Version: 1.0
Content-Type: text/plain; format=flowed
Message-ID: <BAY9-F321zXs0oByC4w0000d0bb@hotmail.com>

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