pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedconverting an oracle procedure to postgres
3+ messages / 3 participants
[nested] [flat]
* converting an oracle procedure to postgres
@ 2003-10-20 21:20 Clint Stotesbery <cstotes@hotmail.com>
2003-10-20 21:28 ` Re: converting an oracle procedure to postgres Josh Berkus <josh@agliodbs.com>
2003-10-20 22:32 ` Re: converting an oracle procedure to postgres Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Clint Stotesbery @ 2003-10-20 21:20 UTC (permalink / raw)
To: 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_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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: converting an oracle procedure to postgres
2003-10-20 21:20 converting an oracle procedure to postgres Clint Stotesbery <cstotes@hotmail.com>
@ 2003-10-20 21:28 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2003-10-20 21:28 UTC (permalink / raw)
To: Clint Stotesbery <cstotes@hotmail.com>; pgsql-sql
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 Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: converting an oracle procedure to postgres
2003-10-20 21:20 converting an oracle procedure to postgres Clint Stotesbery <cstotes@hotmail.com>
@ 2003-10-20 22:32 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2003-10-20 22:32 UTC (permalink / raw)
To: Clint Stotesbery <cstotes@hotmail.com>; +Cc: pgsql-sql
"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: fooey
fooey
-------
(1 row)
regression=#
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-10-20 22:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-20 21:20 converting an oracle procedure to postgres Clint Stotesbery <cstotes@hotmail.com>
2003-10-20 21:28 ` Josh Berkus <josh@agliodbs.com>
2003-10-20 22:32 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox