Help converting Oracle instead of triggers to PostgreSQL

Поиск
Список
Период
Сортировка
От Clint Stotesbery
Тема Help converting Oracle instead of triggers to PostgreSQL
Дата
Msg-id BAY9-F321zXs0oByC4w0000d0bb@hotmail.com
обсуждение исходный текст
Ответы Re: Help converting Oracle instead of triggers to PostgreSQL  (Christoph Haller <ch@rodos.fzk.de>)
Список pgsql-sql
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



В списке pgsql-sql по дате отправления:

Предыдущее
От: Guy Fraser
Дата:
Сообщение: Re: Datatype Inet and Searching
Следующее
От: Christoph Haller
Дата:
Сообщение: Re: Help converting Oracle instead of triggers to PostgreSQL