[Newbie] migrating a stored procedure from MSSQL to postgresql
| От | Bengali |
|---|---|
| Тема | [Newbie] migrating a stored procedure from MSSQL to postgresql |
| Дата | |
| Msg-id | bhj1li$1d7$1@biggoron.nerim.net обсуждение исходный текст |
| Список | 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)
asset nocount on
update ManufacturerOrders set Status = "Shipped" whereOrderNumber = @OrderNum;
SELECT SKU, Price FROM ManufacturerOrdersWHERE 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
В списке pgsql-sql по дате отправления: