Hello
2012/2/2 F. BROUARD / SQLpro <sqlpro@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
WHEREUserDataAcountId= _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
