From: | "Clint Stotesbery" <cstotes(at)hotmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Help converting Oracle instead of triggers to PostgreSQL |
Date: | 2003-12-03 19:34:15 |
Message-ID: | BAY9-F321zXs0oByC4w0000d0bb@hotmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Christoph Haller | 2003-12-04 17:16:32 | Re: Help converting Oracle instead of triggers to PostgreSQL |
Previous Message | Guy Fraser | 2003-12-03 18:32:47 | Re: Datatype Inet and Searching |