Re: Help converting Oracle instead of triggers to PostgreSQL - Mailing list pgsql-sql

From Christoph Haller
Subject Re: Help converting Oracle instead of triggers to PostgreSQL
Date
Msg-id 200312041616.RAA29744@rodos
Whole thread Raw
In response to Help converting Oracle instead of triggers to PostgreSQL  ("Clint Stotesbery" <cstotes@hotmail.com>)
List 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 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.
> 
Not sure if this is of any help ... 
AFAIK there are no updatable views in pg. 
But aside from that, I cannot see nothing what could not be 
done by a pg trigger function: 
CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }   ON table FOR EACH { ROW | STATEMENT }EXECUTE PROCEDURE func
(arguments )
 

Also try 

http://techdocs.postgresql.org/#convertfrom
Converting from other Databases to PostgreSQL 

and/or 

http://openacs.org/search/search?q=oracle+to+pg+porting&t=Search

HTH

Regards, Christoph 



pgsql-sql by date:

Previous
From: "Clint Stotesbery"
Date:
Subject: Help converting Oracle instead of triggers to PostgreSQL
Next
From: "Clint Stotesbery"
Date:
Subject: Re: Help converting Oracle instead of triggers to PostgreSQL