Hello 2012/2/2 F. BROUARD / SQLpro <sql...@club-internet.fr>: > 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.
It is not exact in this case - it is error handling - and plpgsql supports it - but you can't to rewrite PL code to PostgreSQL one to one. Regards Pavel CREATE OR REPLACE FUNCTION DeleteUserData(ACDetailsID int[]) RETURNS void AS $$ DECLARE _id int; BEGIN -- postgresql has no table type, use a int array instead FOR _id IN array_lower(ACDetailsID,1) .. array_upper(ACDetailsID,1) LOOP DELETE FROM UserAccountDetails WHERE UserDataAcountId= _id; DELETE FROM UserAC WHERE UserDataAcountId= _id; END; END LOOP; EXCEPTION WHEN OTHERS THEN RAISE WARNING 'some issue %', SQLERRM; END; $$ LANGUAGE plpgsql; or little bit more effective code CREATE OR REPLACE FUNCTION DeleteUserData(ACDetailsID int[]) RETURNS void AS $$ DECLARE _id int; BEGIN -- postgresql has no table type, use a int array instead DELETE FROM UserAccountDetails WHERE UserDataAcountId= ANY(ACDetailsID); DELETE FROM UserAC WHERE UserDataAcountId= ANY(ACDetailsID); EXCEPTION WHEN OTHERS THEN RAISE WARNING 'some issue %', SQLERRM; END; $$ LANGUAGE plpgsql; > > 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 ************************* > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql