From: | Bengali <lyngo_fr(at)yahoo(dot)fr> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | [Newbie] migrating a stored procedure from MSSQL to postgresql |
Date: | 2003-08-15 16:27:36 |
Message-ID: | bhj1li$1d7$1@biggoron.nerim.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi,
I am a postgresql and stored procedures beginner and I
would like to know if the stored procedure I am trying to migrate
to plpgsql from MSSQL is correct.
Here 's the only table involved in the stored procedure:
create table ManufacturerOrders
(
OrderNumber serial,
SKU int not null,
Make varchar(50) not null,
Model varchar(50) not null,
Price int not null,
Status varchar(20) not null,
primary key (OrderNumber)
);
Here 's the original MSSQL stored procedure:
create procedure UpdateOrder (@OrderNum int)
as
set nocount on
update ManufacturerOrders set Status = "Shipped" where
OrderNumber = @OrderNum;
SELECT SKU, Price FROM ManufacturerOrders
WHERE OrderNumber = @OrderNum
go
Here 's the plpgsql version i wrote:
CREATE FUNCTION UpdateOrder(INTEGER) RETURNS TEXT AS '
DECLARE
i_ordernum ALIAS for $1;
r_SKUPrice RECORD;
BEGIN
update ManufacturerOrders set Status = ''Shipped'' where
OrderNumber = i_ordernum;
SELECT SKU, Price INTO r_SKUPrice FROM ManufacturerOrders WHERE
OrderNumber = i_ordernum;
return r_SKUPrice;
END;
' LANGUAGE 'plpgsql';
I would like to know especially if the RETURNS statement is correct here
and if i can give a name to the record r_SKUPrice columns .
Thanks in advance,
Bengali
From | Date | Subject | |
---|---|---|---|
Next Message | ProgHome | 2003-08-15 20:24:24 | Re: How to optimize this query ? |
Previous Message | Tom Lane | 2003-08-15 15:57:40 | Re: About primary keys. |