X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 44983D1B8B5
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 20 Aug 2003 20:02:10 -0300 (ADT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 41953-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 20 Aug 2003 20:02:01 -0300 (ADT)
Received: from abe.micropat.com (abe.micropat.com [204.17.221.119])
	by svr1.postgresql.org (Postfix) with ESMTP id B8009D1BAEA
	for <pgsql-sql@postgresql.org>; Wed, 20 Aug 2003 20:01:54 -0300 (ADT)
Received: from micropat.com ([204.17.221.90]) by
	abe.micropat.com (Netscape Messaging Server 4.15) with ESMTP id
	HJXV0V00.GNR for <pgsql-sql@postgresql.org>; Wed, 20 Aug 2003
	18:12:31 -0400
Message-ID: <3F43F615.D94FF744@micropat.com>
Date: Wed, 20 Aug 2003 18:28:37 -0400
From: "Richard Hall" <rhall@micropat.com>
Organization: Micropatent, LLC
X-Mailer: Mozilla 4.79 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Cc: pgsql-sql@postgresql.org
Subject: Re: [Newbie] migrating a stored procedure from MSSQL to postgresql
References: <bhj1li$1d7$1@biggoron.nerim.net>
Content-Type: multipart/alternative;
	boundary="------------EA2DF53696438223418E731F"
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200308/256
X-Sequence-Number: 14872

--------------EA2DF53696438223418E731F
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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

--------------EA2DF53696438223418E731F
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
As declared, your function returns TEXT, i.e. unlimited characters.
<br><tt>>>&nbsp; CREATE FUNCTION UpdateOrder(INTEGER) RETURNS <b>TEXT</b>
AS</tt>
<p>Since your variable
<br>>>&nbsp; <tt>r_SKUPrice RECORD;</tt>
<br>contains a number of columns
<br><tt>>>&nbsp; SELECT SKU, Price INTO r_SKUPrice</tt>
<br>you could create a composite TYPE that matches those columns
<p>and
<br>since your variable can contain a number of such rows, (see the select
above)
<br>the function needs to become a set returning function
<p>CREATE FUNCTION UpdateOrder(INTEGER) RETURNS SETOF &lt;your_type_here>
AS
<p>Rick
<br>&nbsp;
<p>Bengali wrote:
<blockquote TYPE=CITE>Hi,
<br>I am a postgresql and 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>&nbsp;&nbsp;&nbsp;&nbsp; OrderNumber serial,
<br>&nbsp;&nbsp;&nbsp;&nbsp; SKU&nbsp;&nbsp;&nbsp;&nbsp; int not null,
<br>&nbsp;&nbsp;&nbsp;&nbsp; Make&nbsp;&nbsp;&nbsp; varchar(50) not null,
<br>&nbsp;&nbsp;&nbsp;&nbsp; Model&nbsp;&nbsp; varchar(50) not null,
<br>&nbsp;&nbsp;&nbsp;&nbsp; Price&nbsp;&nbsp; int not null,
<br>&nbsp;&nbsp;&nbsp;&nbsp; Status varchar(20) not null,
<br>&nbsp;&nbsp;&nbsp;&nbsp; primary key (OrderNumber)
<br>);
<p>Here 's the original MSSQL stored procedure:
<br>create procedure UpdateOrder (@OrderNum int)
<br>as
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; set nocount on
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; update ManufacturerOrders
set Status = "Shipped" where
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OrderNumber = @OrderNum;
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT SKU, Price FROM ManufacturerOrders
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE OrderNumber = @OrderNum
<br>go
<p>Here 's the plpgsql version i wrote:
<p>CREATE FUNCTION UpdateOrder(INTEGER) RETURNS TEXT AS '
<br>&nbsp; DECLARE
<br>&nbsp;&nbsp; i_ordernum ALIAS for $1;
<br>&nbsp;&nbsp; r_SKUPrice RECORD;
<br>&nbsp; BEGIN
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; update ManufacturerOrders
set Status = ''Shipped'' where
<br>OrderNumber = i_ordernum;
<p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT SKU, Price INTO
r_SKUPrice FROM ManufacturerOrders WHERE
<br>OrderNumber = i_ordernum;
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return r_SKUPrice;
<p>&nbsp; END;
<br>&nbsp; ' LANGUAGE 'plpgsql';
<p>I would like to know especially 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>TIP 7: don't forget to increase your free space map settings</blockquote>
</html>

--------------EA2DF53696438223418E731F--