From: "Clint Stotesbery" <cstotes@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: converting an oracle procedure to postgres
Date: Mon, 20 Oct 2003 21:20:32 +0000
Mime-Version: 1.0
Content-Type: text/plain; format=flowed
Message-ID: <BAY9-F31GnRtU4RFuiP0001795c@hotmail.com>

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_shippingrate decimal;
      V_shippingcharge decimal := 0;
      v_taxrate   decimal := 0;
      v_taxamt    decimal;
      v_totalamt decimal;
   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 distinct customer_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