[Newbie] migrating a stored procedure from MSSQL to postgresql

From: Bengali <lyngo_fr(at)yahoo(dot)fr>
To: pgsql-sql(at)postgresql(dot)org
Subject: [Newbie] migrating a stored procedure from MSSQL to postgresql
Date: 2003-08-15 16:27:36
Message-ID: bhj1li$1d7$1@biggoron.nerim.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message ProgHome 2003-08-15 20:24:24 Re: How to optimize this query ?
Previous Message Tom Lane 2003-08-15 15:57:40 Re: About primary keys.