Re: MS-SQL Store Procedure to Postgresql Function

Hello

2012/2/2 F. BROUARD / SQLpro <sqlpro(at)club-internet(dot)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(at)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=(at)ID
>> delete from UserAC where UserDataAcountId=(at)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql