Thread: Help converting Oracle instead of triggers to PostgreSQL

Help converting Oracle instead of triggers to PostgreSQL

From
"Clint Stotesbery"
Date:
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



Re: Help converting Oracle instead of triggers to PostgreSQL

From
Christoph Haller
Date:
> 
> 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 



Re: Help converting Oracle instead of triggers to PostgreSQL

From
"Clint Stotesbery"
Date:
Hi Christoph,
Thanks for the links but the techdoc links for converting from Oracle to 
PostgreSQL has 2 links that don't go to their intended targets anymore, one 
is in the 7.3 docs which is really limited (only covers simple things), and 
the Ora2Pg one I don't really get that well.

As far as updateable views, that's why you need instead of triggers. Regular 
triggers can't be done on views. So if I make an instead of trigger on a 
view that's for updates then I have an updateable view. I figured it out 
last night and I was along the right track in my original post with using an 
instead of rule to call a function. The trick is that I have to pass in all 
the old.col and new.col stuff into the function that I call from the rule. 
In Oracle since the instead of stuff is a trigger I had access to the 
new.col and old.col stuff. To do it in PostgreSQL rules I had to pass it all 
in. I'm going to write a doc about the problems I've encountered during my 
conversion project and then submit it to the Postgres people I think (to who 
though?). My programming and tech writing background should help I hope. 
Thanks for the suggestions Christoph.
-Clint


----Original Message Follows----
From: Christoph Haller <ch@rodos.fzk.de>
To: cstotesbery@acm.org
CC: pgsql-sql@postgresql.org
Subject: Re: [SQL] Help converting Oracle instead of triggers to PostgreSQL
Date: Thu, 04 Dec 2003 17:16:32 MET

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
               http://www.postgresql.org/docs/faqs/FAQ.html

_________________________________________________________________
Get holiday tips for festive fun. 
http://special.msn.com/network/happyholidays.armx



Re: Help converting Oracle instead of triggers to PostgreSQL

From
Robert Treat
Date:
Generally speaking you can send articles to me or to webmaster@postgresql.org 
for inclusion on the techdocs site.  

I'll try to update the links you mentioned below as well. thanks.

Robert Treat

On Thursday 04 December 2003 12:52, Clint Stotesbery wrote:
> Hi Christoph,
> Thanks for the links but the techdoc links for converting from Oracle to
> PostgreSQL has 2 links that don't go to their intended targets anymore, one
> is in the 7.3 docs which is really limited (only covers simple things), and
> the Ora2Pg one I don't really get that well.
>
> As far as updateable views, that's why you need instead of triggers.
> Regular triggers can't be done on views. So if I make an instead of trigger
> on a view that's for updates then I have an updateable view. I figured it
> out last night and I was along the right track in my original post with
> using an instead of rule to call a function. The trick is that I have to
> pass in all the old.col and new.col stuff into the function that I call
> from the rule. In Oracle since the instead of stuff is a trigger I had
> access to the new.col and old.col stuff. To do it in PostgreSQL rules I had
> to pass it all in. I'm going to write a doc about the problems I've
> encountered during my conversion project and then submit it to the Postgres
> people I think (to who though?). My programming and tech writing background
> should help I hope. Thanks for the suggestions Christoph.
> -Clint
>
>
> ----Original Message Follows----
> From: Christoph Haller <ch@rodos.fzk.de>
> To: cstotesbery@acm.org
> CC: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Help converting Oracle instead of triggers to PostgreSQL
> Date: Thu, 04 Dec 2003 17:16:32 MET
>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                 http://www.postgresql.org/docs/faqs/FAQ.html
>
> _________________________________________________________________
> Get holiday tips for festive fun.
> http://special.msn.com/network/happyholidays.armx
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html

-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


Re: Help converting Oracle instead of triggers to PostgreSQL

From
Robert Treat
Date:
Just to follow up I managed to track down these missing articles and have 
updated the links on the website.

Robert Treat

On Thursday 04 December 2003 12:52, Clint Stotesbery wrote:
> Hi Christoph,
> Thanks for the links but the techdoc links for converting from Oracle to
> PostgreSQL has 2 links that don't go to their intended targets anymore, one
> is in the 7.3 docs which is really limited (only covers simple things), and
> the Ora2Pg one I don't really get that well.
>
> As far as updateable views, that's why you need instead of triggers.
> Regular triggers can't be done on views. So if I make an instead of trigger
> on a view that's for updates then I have an updateable view. I figured it
> out last night and I was along the right track in my original post with
> using an instead of rule to call a function. The trick is that I have to
> pass in all the old.col and new.col stuff into the function that I call
> from the rule. In Oracle since the instead of stuff is a trigger I had
> access to the new.col and old.col stuff. To do it in PostgreSQL rules I had
> to pass it all in. I'm going to write a doc about the problems I've
> encountered during my conversion project and then submit it to the Postgres
> people I think (to who though?). My programming and tech writing background
> should help I hope. Thanks for the suggestions Christoph.
> -Clint
>
>
> ----Original Message Follows----
> From: Christoph Haller <ch@rodos.fzk.de>
> To: cstotesbery@acm.org
> CC: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Help converting Oracle instead of triggers to PostgreSQL
> Date: Thu, 04 Dec 2003 17:16:32 MET
>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                 http://www.postgresql.org/docs/faqs/FAQ.html
>
> _________________________________________________________________
> Get holiday tips for festive fun.
> http://special.msn.com/network/happyholidays.armx
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html

-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL