Help converting Oracle instead of triggers to PostgreSQL

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

Responses

Browse pgsql-sql by date

  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