From: F. BROUARD / SQLpro <sqlpro@club-internet.fr>
To: pgsql-sql@postgresql.org
Subject: Re: MS-SQL Store Procedure to Postgresql Function
Date: Thu, 02 Feb 2012 22:00:45 +0100
Message-ID: <4F2AF97D.50602@club-internet.fr> (raw)
In-Reply-To: <1327905771.73991.YahooMailNeo@web121606.mail.ne1.yahoo.com>
References: <1327905771.73991.YahooMailNeo@web121606.mail.ne1.yahoo.com>
Actullay there is no transaction support in internal PG routines.
So the code you posted is not translatable in PG PL/SQL because it
involve a transaction inside the process.
A +
Le 30/01/2012 07:42, Rehan Saleem a écrit :
> hi , how i can convert this store procedure to PostgreSQL function,
> especially I really dont know how to set type to readonly in PostgreSQL.
> thanks
>
>
> ALTERPROCEDURE [dbo].[sp_DeleteUserData]
> @ACDetailsID dbo.ACdetailsID_type READONLY
> AS
> DECLARE@ID int
> begintry
> begintransaction
> DECLARE c_ACDetailsID CURSOR FOR SELECT id FROM @ACDetailsID
> OPEN c_ACDetailsID
> FETCH NEXT FROM c_ACDetailsID INTO @ID
> WHILE (@@FETCH_STATUS = 0) BEGIN
> delete from UserAccountDetails where UserDataAcountId=@ID
> delete from UserAC where UserDataAcountId=@ID
> FETCH NEXT FROM c_ACDetailsID INTO @ID
> End--end of while loop
> committransaction
> CLOSEc_ACDetailsID
> DEALLOCATEc_ACDetailsID
> endtry
> begincatch
> rollback transaction;
> print error_message(
--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************
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: sqlpro@club-internet.fr
Subject: Re: MS-SQL Store Procedure to Postgresql Function
In-Reply-To: <4F2AF97D.50602@club-internet.fr>
* 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