Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 472C11EC5BE8 for ; Thu, 2 Feb 2012 17:01:14 -0400 (AST) Received: from smtp4-g21.free.fr ([2a01:e0c:1:1599::13]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Rt3mK-0001tl-0x for pgsql-sql@postgresql.org; Thu, 02 Feb 2012 21:01:14 +0000 Received: from [192.168.0.89] (unknown [78.237.44.8]) by smtp4-g21.free.fr (Postfix) with ESMTP id 96C134C80BB for ; Thu, 2 Feb 2012 22:00:53 +0100 (CET) Message-ID: <4F2AF97D.50602@club-internet.fr> Date: Thu, 02 Feb 2012 22:00:45 +0100 From: "F. BROUARD / SQLpro" Organization: sqlpro.developpez.com User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:9.0) Gecko/20111222 Thunderbird/9.0.1 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: MS-SQL Store Procedure to Postgresql Function References: <1327905771.73991.YahooMailNeo@web121606.mail.ne1.yahoo.com> In-Reply-To: <1327905771.73991.YahooMailNeo@web121606.mail.ne1.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201202/10 X-Sequence-Number: 36303 Actullay there is no transaction support in internal PG routines. So the code you posted is not translatable in PG PL/SQL because it=20 involve a transaction inside the process. A + Le 30/01/2012 07:42, Rehan Saleem a =E9crit : > 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 =3D 0) BEGIN > delete from UserAccountDetails where UserDataAcountId=3D@ID > delete from UserAC where UserDataAcountId=3D@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( --=20 Fr=E9d=E9ric 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=E9tiers PACA, ISEN Toulon et CESI/EXIA Aix en Provenc= e Audit, conseil, expertise, formation, mod=E9lisation, tuning, optimisatio= n *********************** http://www.sqlspot.com *************************