converting an oracle procedure to postgres - Mailing list pgsql-sql
From | Clint Stotesbery |
---|---|
Subject | converting an oracle procedure to postgres |
Date | |
Msg-id | BAY9-F31GnRtU4RFuiP0001795c@hotmail.com Whole thread Raw |
Responses |
Re: converting an oracle procedure to postgres
Re: converting an oracle procedure to postgres |
List | pgsql-sql |
I have read the docs on converting Oracle to Postgres already. I'm a little confused though. I have a procedure in Oracle that just does some calculations and then does an update based on the calculations. The procedure doesn't return anything. It seems like in Postgres that everything has to be a function and has to return something. The following procedure is almost converted to Postgres format but it is not quite correct yet: CREATE OR REPLACE PROCEDURE p_updateorders (decimal, date) AS ' DECLARE orderno ALIAS FOR $1; orderdate ALIAS FOR$2; --defining variables v_subtotal decimal; v_taxstatus varchar(1); v_shipping varchar(12); v_shippingratedecimal; V_shippingcharge decimal := 0; v_taxrate decimal := 0; v_taxamt decimal; v_totalamtdecimal; BEGIN --taking the subtotal by calcualting with right price and qty of products in an order SELECT SUM( product_price(orderdate,product_no, qty) * qty) INTO v_subtotal FROM orderline WHERE order_no = orderno GROUP BY order_no; --finding if tax applicable or not SELECT tax_status INTO v_taxstatus FROM orders WHERE order_no =orderno; --finding the shipping method SELECT shipping_method INTO v_shipping FROM orders WHERE order_no =orderno; --get the tax rate IF upper(v_taxstatus) = ''Y'' THEN SELECT tax_rate INTO v_taxrate FROM tax WHERE state = (SELECT state FROM customer WHERE customer_no = (SELECT distinctcustomer_no FROM orders WHERE order_no = orderno)); END IF; v_taxamt := v_taxrate * v_subtotal; --get shipping cost IF upper(v_shipping) = ''2DAY-AIR'' THEN v_shippingrate := .08; ELSIF upper(v_shipping)= ''1DAY-AIR'' THEN v_shippingrate := .1; ELSIF upper(v_shipping) = ''GROUND'' THEN v_shippingrate:= .05; ELSE v_shippingrate := 0; END IF; v_shippingcharge := v_shippingrate * v_subtotal; --calculating the total amount v_totalamt := v_subtotal + v_taxamt + v_shippingcharge; --now update the ORDERS table with new values UPDATE orders SET subtotal = v_subtotal, tax_amt =v_taxamt, shipping_charge = v_shippingcharge, total_amt = v_totalamt WHERE order_no = orderno; END; ' LANGUAGE 'plpgsql'; I know I have to relpace the word PROCEDURE with FUNCTION but then it wants me to put RETURNS <datatype> but I don't want to return anything. I was thinking that I could just have it return integer and then after the last update statement put return 0. I'd rather not have it return a junk value though. What should I do? Thanks, Clint _________________________________________________________________ Never get a busy signal because you are always connected with high-speed Internet access. Click here to comparison-shop providers. https://broadband.msn.com