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
Message-ID: <BAY9-F321zXs0oByC4w0000d0bb@hotmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cstotes@hotmail.com
Subject: Re: Help converting Oracle instead of triggers to PostgreSQL
In-Reply-To: <BAY9-F321zXs0oByC4w0000d0bb@hotmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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