Обсуждение: converting an oracle procedure to postgres

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

Clint,

>       v_taxstatus  varchar(1);
>       v_shipping   varchar(12);

Drop the varchar limits, it's not supported inside PL/pgSQL.

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

I generally return integer or boolean for such functions.  For example, you
can have it return "TRUE" at the end, and then your client code can interpret
any non-true result (Error message, null) as an error.

--
-Josh BerkusAglio Database SolutionsSan Francisco

"Clint Stotesbery" <cstotes@hotmail.com> writes:
> 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.

You can say RETURNS VOID in recent releases.  This is a bit of a hack
but it expresses your intent ... you still have to use SELECT to invoke
the function though.

regression=# create or replace function fooey () returns void as '
regression'# begin
regression'# raise notice ''fooey'';
regression'# return;
regression'# end' language plpgsql;
CREATE FUNCTION
regression=# select fooey();
NOTICE:  fooeyfooey
-------

(1 row)

regression=#

        regards, tom lane