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>
