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

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

As declared, your function returns TEXT, i.e. unlimited characters. <br /><tt>>>  CREATE FUNCTION
UpdateOrder(INTEGER)RETURNS <b>TEXT</b> AS</tt><p>Since your variable <br />>>  <tt>r_SKUPrice RECORD;</tt><br
/>containsa number of columns <br /><tt>>>  SELECT SKU, Price INTO r_SKUPrice</tt><br />you could create a
compositeTYPE that matches those columns <p>and <br />since your variable can contain a number of such rows, (see the
selectabove) <br />the function needs to become a set returning function <p>CREATE FUNCTION UpdateOrder(INTEGER)
RETURNSSETOF <your_type_here> AS <p>Rick <br />  <p>Bengali wrote: <blockquote type="CITE">Hi, <br />I am a
postgresqland stored procedures beginner and I <br />would like to know if the stored procedure I am trying to migrate
<br/>to plpgsql from MSSQL is correct. <p>Here 's the only table involved in the stored procedure: <br />create table
ManufacturerOrders<br />( <br />     OrderNumber serial, <br />     SKU     int not null, <br />     Make   
varchar(50)not null, <br />     Model   varchar(50) not null, <br />     Price   int not null, <br />     Status
varchar(20)not null, <br />     primary key (OrderNumber) <br />); <p>Here 's the original MSSQL stored procedure: <br
/>createprocedure UpdateOrder (@OrderNum int) <br />as <br />        set nocount on <p>        update
ManufacturerOrdersset Status = "Shipped" where <br />        OrderNumber = @OrderNum; <p>        SELECT SKU, Price FROM
ManufacturerOrders<br />        WHERE OrderNumber = @OrderNum <br />go <p>Here 's the plpgsql version i wrote:
<p>CREATEFUNCTION UpdateOrder(INTEGER) RETURNS TEXT AS ' <br />  DECLARE <br />   i_ordernum ALIAS for $1; <br />  
r_SKUPriceRECORD; <br />  BEGIN <br />         update ManufacturerOrders set Status = ''Shipped'' where <br
/>OrderNumber= i_ordernum; <p>         SELECT SKU, Price INTO r_SKUPrice FROM ManufacturerOrders WHERE <br
/>OrderNumber= i_ordernum; <br />         return r_SKUPrice; <p>  END; <br />  ' LANGUAGE 'plpgsql'; <p>I would like to
knowespecially if the RETURNS statement is correct here <br />and if i can give a name to the record r_SKUPrice columns
.<p>Thanks in advance, <br />Bengali <p>---------------------------(end of broadcast)--------------------------- <br
/>TIP7: don't forget to increase your free space map settings</blockquote>