Re: Help converting Oracle instead of triggers to PostgreSQL - Mailing list pgsql-sql
From | Christoph Haller |
---|---|
Subject | Re: Help converting Oracle instead of triggers to PostgreSQL |
Date | |
Msg-id | 200312041616.RAA29744@rodos Whole thread Raw |
In response to | Help converting Oracle instead of triggers to PostgreSQL ("Clint Stotesbery" <cstotes@hotmail.com>) |
List | 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