pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

[Newbie] migrating a stored procedure from MSSQL to postgresql
2+ messages / 2 participants
[nested] [flat]

* [Newbie] migrating a stored procedure from MSSQL to postgresql
@ 2003-08-15 16:27 Bengali <lyngo_fr@yahoo.fr>
  2003-08-20 22:28 ` Re: [Newbie] migrating a stored procedure from MSSQL to postgresql Richard Hall <rhall@micropat.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Bengali @ 2003-08-15 16:27 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: [Newbie] migrating a stored procedure from MSSQL to postgresql
  2003-08-15 16:27 [Newbie] migrating a stored procedure from MSSQL to postgresql Bengali <lyngo_fr@yahoo.fr>
@ 2003-08-20 22:28 ` Richard Hall <rhall@micropat.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Richard Hall @ 2003-08-20 22:28 UTC (permalink / raw)
  To: ; +Cc: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-08-20 22:28 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-15 16:27 [Newbie] migrating a stored procedure from MSSQL to postgresql Bengali <lyngo_fr@yahoo.fr>
2003-08-20 22:28 ` Richard Hall <rhall@micropat.com>

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