Re: Help converting Oracle instead of triggers to PostgreSQL

>
> 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