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

[Richard Hall]

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