[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 по дате отправления: