From: Richard Hall <rhall@micropat.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: [Newbie] migrating a stored procedure from MSSQL to postgresql
Date: Wed, 20 Aug 2003 18:28:37 -0400
Message-ID: <3F43F615.D94FF744@micropat.com> (raw)
References: <bhj1li$1d7$1@biggoron.nerim.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rhall@micropat.com
Subject: Re: [Newbie] migrating a stored procedure from MSSQL to postgresql
In-Reply-To: <3F43F615.D94FF744@micropat.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox