Re: [Newbie] migrating a stored procedure from MSSQL to postgresql

As declared, your function returns TEXT, i.e. unlimited characters.
>> CREATE FUNCTION UpdateOrder(INTEGER) RETURNS TEXT AS

Since your variable
>> r_SKUPrice RECORD;
contains a number of columns
>> SELECT SKU, Price INTO r_SKUPrice
you could create a composite TYPE that matches those columns

and
since your variable can contain a number of such rows, (see the select
above)
the function needs to become a set returning function

CREATE FUNCTION UpdateOrder(INTEGER) RETURNS SETOF <your_type_here> AS

Rick

Bengali wrote:

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings