pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMS-SQL Store Procedure to Postgresql Function
4+ messages / 4 participants
[nested] [flat]
* MS-SQL Store Procedure to Postgresql Function
@ 2012-01-30 06:42 Rehan Saleem <pk_rehan@yahoo.com>
0 siblings, 2 replies; 4+ messages in thread
From: Rehan Saleem @ 2012-01-30 06:42 UTC (permalink / raw)
To: pgsql-sql
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(
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: MS-SQL Store Procedure to Postgresql Function
@ 2012-02-01 10:52 Jasen Betts <jasen@xnet.co.nz>
parent: Rehan Saleem <pk_rehan@yahoo.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jasen Betts @ 2012-02-01 10:52 UTC (permalink / raw)
To: pgsql-sql
On 2012-01-30, Rehan Saleem <pk_rehan@yahoo.com> wrote:
>hi , how i can convert this store procedure to PostgreSQL function,
>especially I really dont know how to set type to readonly in PostgreSQL.
>thanksUserACUserAC
I don't actually understand T-SQL so I'm going from the
structure more than from the actual meaning of the code given
it looks like the same effect could be had in a different way
"delete ... using" instead.
this is assuming that you set up a cascading delete of
UserAccountDetails
assuming the thie makes sense:
ALTER TABLE UserAC ADD PRIMARY KEY(UserDataAcountId);
-- the important part is UserAC.UserDataAcountId has an index
-- with the unique property, I'm guessing id is actually a primary key.
ALTER TABLE UserAccountDetails ADD UserDataAcountId REFERENCES
UserAC(UserDataAcountId) ON DELETE CASCADE;
-- again i'm guessing from the names of the columns.
-- nut if that all fits your data, and usage patterns
-- you can do the following:
DELETE FROM UserAC USING /*some-query*/ WHERE /*some-condition*/;
and get pretty-much the same effect.
here /*some-query*/ would be your table expression and
/*some-condition*/ would join it to UserAC
postgresql treats all unquoted identifiers as lower case, if you are
have stuff with mixed-case names you need to quote them with double
quotes, I have assumend that the mixed case is just for menmonic
reasons and not significant.
although postgres doesn't do table parameters there are three
other possibilities,
dynamic sql
refcursors
agregate functions.
thses are all considered advanced topics,
expect EVERYTHING that is not covered by SQL standards to be totally
different,
there is usually a way to do what you want, it may well be completely
different to the old way.
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: MS-SQL Store Procedure to Postgresql Function
@ 2012-02-02 21:00 F. BROUARD / SQLpro <sqlpro@club-internet.fr>
parent: Rehan Saleem <pk_rehan@yahoo.com>
1 sibling, 1 reply; 4+ messages in thread
From: F. BROUARD / SQLpro @ 2012-02-02 21:00 UTC (permalink / raw)
To: pgsql-sql
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 *************************
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: MS-SQL Store Procedure to Postgresql Function
@ 2012-02-02 21:20 Pavel Stehule <pavel.stehule@gmail.com>
parent: F. BROUARD / SQLpro <sqlpro@club-internet.fr>
0 siblings, 0 replies; 4+ messages in thread
From: Pavel Stehule @ 2012-02-02 21:20 UTC (permalink / raw)
To: F. BROUARD / SQLpro <sqlpro@club-internet.fr>; +Cc: pgsql-sql
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 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-02-02 21:20 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-30 06:42 MS-SQL Store Procedure to Postgresql Function Rehan Saleem <pk_rehan@yahoo.com>
2012-02-01 10:52 ` Jasen Betts <jasen@xnet.co.nz>
2012-02-02 21:00 ` F. BROUARD / SQLpro <sqlpro@club-internet.fr>
2012-02-02 21:20 ` Pavel Stehule <pavel.stehule@gmail.com>
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