pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp converting Oracle instead of triggers to PostgreSQL
5+ messages / 3 participants
[nested] [flat]
* Help converting Oracle instead of triggers to PostgreSQL
@ 2003-12-03 19:34 Clint Stotesbery <cstotes@hotmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Clint Stotesbery @ 2003-12-03 19:34 UTC (permalink / raw)
To: 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.
_________________________________________________________________
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help converting Oracle instead of triggers to PostgreSQL
@ 2003-12-04 17:16 Christoph Haller <ch@rodos.fzk.de>
parent: Clint Stotesbery <cstotes@hotmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Christoph Haller @ 2003-12-04 17:16 UTC (permalink / raw)
To: cstotesbery@acm.org; +Cc: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help converting Oracle instead of triggers to PostgreSQL
@ 2003-12-04 17:52 Clint Stotesbery <cstotes@hotmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Clint Stotesbery @ 2003-12-04 17:52 UTC (permalink / raw)
To: ch@rodos.fzk.de; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help converting Oracle instead of triggers to PostgreSQL
@ 2003-12-06 23:45 Robert Treat <xzilla@users.sourceforge.net>
parent: Clint Stotesbery <cstotes@hotmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Robert Treat @ 2003-12-06 23:45 UTC (permalink / raw)
To: cstotesbery@acm.org, "Clint Stotesbery" <cstotes@hotmail.com>; ch@rodos.fzk.de; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help converting Oracle instead of triggers to PostgreSQL
@ 2003-12-13 14:55 Robert Treat <xzilla@users.sourceforge.net>
parent: Clint Stotesbery <cstotes@hotmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Robert Treat @ 2003-12-13 14:55 UTC (permalink / raw)
To: cstotesbery@acm.org, "Clint Stotesbery" <cstotes@hotmail.com>; ch@rodos.fzk.de; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-12-13 14:55 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-03 19:34 Help converting Oracle instead of triggers to PostgreSQL Clint Stotesbery <cstotes@hotmail.com>
2003-12-04 17:16 ` Christoph Haller <ch@rodos.fzk.de>
2003-12-04 17:52 Re: Help converting Oracle instead of triggers to PostgreSQL Clint Stotesbery <cstotes@hotmail.com>
2003-12-06 23:45 ` Robert Treat <xzilla@users.sourceforge.net>
2003-12-13 14:55 ` Robert Treat <xzilla@users.sourceforge.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox