Help converting Oracle instead of triggers to PostgreSQL
От | Clint Stotesbery |
---|---|
Тема | Help converting Oracle instead of triggers to PostgreSQL |
Дата | |
Msg-id | BAY9-F321zXs0oByC4w0000d0bb@hotmail.com обсуждение исходный текст |
Ответы |
Re: Help converting Oracle instead of triggers to PostgreSQL
|
Список | 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 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
В списке pgsql-sql по дате отправления: