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

Предыдущее
От: David Fetter
Дата:
Сообщение: Re: Optional join
Следующее
От: Tom Lane
Дата:
Сообщение: Re: About primary keys -- made some progress