pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to convert SQL store procedure to Postgresql function
5+ messages / 3 participants
[nested] [flat]
* How to convert SQL store procedure to Postgresql function
@ 2012-02-28 08:50 Rehan Saleem <pk_rehan@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Rehan Saleem @ 2012-02-28 08:50 UTC (permalink / raw)
To: pgsql-sql
hi ,
how can i convert this sql store procedure to postgresql function , i shall be very thankful to you, as i am new to postgresql and i dont know how to handle this kind of store procedure in postgresql
thanks
ALTERPROCEDURE [dbo].[sp_GetUserByID]
@UserId varchar(50),
@KBId varchar(50),
@phone varchar(50),
@mhr varchar(50),
@Distance varchar(50)='',
@Total varchar(50) output
AS
BEGIN
DECLARE @sql nvarchar(500);
if (@Distance='')
set @Distance = 1
set @sql = 'select mhr_U, begin_U, End_U, areaSize_U, mhr_KB, begin_KB,
End_KB, areaSize_KB, '
set @sql += ' phone, Distance from vwuserdataSites where UserId=' + @UserId
set @sql += ' and phone>=' + @phone
set @sql += ' AND KBId=' + @KBId
if @mhr<>'All' and ISNULL(@mhr,'')<>''
set @sql += ' AND mhr_U=''' + @mhr +''''
if (@Distance<>'')
set @sql += ' AND (Distance<=' + Distance + ' or ' + Distance + '=1) '
set @sql += ' Order by mhr_U, begin_U'
exec(@sql)
set @Total = @@ROWCOUNT
END
GO
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to convert SQL store procedure to Postgresql function
@ 2012-02-28 10:36 Filip Rembiałkowski <plk.zuber@gmail.com>
parent: Rehan Saleem <pk_rehan@yahoo.com>
0 siblings, 2 replies; 5+ messages in thread
From: Filip Rembiałkowski @ 2012-02-28 10:36 UTC (permalink / raw)
To: Rehan Saleem <pk_rehan@yahoo.com>; +Cc: pgsql-sql
On Tue, Feb 28, 2012 at 9:50 AM, Rehan Saleem <pk_rehan@yahoo.com> wrote:
> hi ,
> how can i convert this sql store procedure to postgresql function , i shall
> be very thankful to you, as i am new to postgresql and i dont know how to
> handle this kind of store procedure in postgresql
Most people handle this with user-defined functions (UDF) written in
PL/PgSQL procedural language.
Try to read The Friendly Manual
http://www.postgresql.org/docs/current/static/sql-createfunction.html
http://www.postgresql.org/docs/current/static/plpgsql.html
Don't worry - all Transact-SQL constructs have their equivalent.
Just start rewriting your function and begin asking specific questions
here... People will help.
I would begin with
create or replace function sp_GetUserByID( in_UserId varchar(50), ...)
returns varchar(50)
language plpgsql as $$
declare
...
begin
...
return somevariable;
end;
$$;
HTH,
Filip
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to convert SQL store procedure to Postgresql function
@ 2012-02-28 11:56 Rehan Saleem <pk_rehan@yahoo.com>
parent: Filip Rembiałkowski <plk.zuber@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Rehan Saleem @ 2012-02-28 11:56 UTC (permalink / raw)
To: Filip Rembiałkowski <plk.zuber@gmail.com>; +Cc: pgsql-sql
hi ,
whats wrong with this function , i am getting syntax error which is syntax error at or near "+="
LINE 13: set sql += ' bpoverlap, centredistance from vwchrcomparesit...
^
how this problem can be solved.
thanks
CREATE OR REPLACE FUNCTION getsitesbytfid(user_datadetailid int, kb_id int,bp_overlap int,chr_ varchar ,centre_distance int)
RETURNS table(chr_u varchar,start_u int,end_u int,region_size_u int,chr_kb varchar,start_kb int ,end_kb int,region_size_kb int,bpoverlap int,centredistance int)
as
$BODY$
DECLARE sql varchar ;
BEGIN
if centre_distance= NULL THEN
set centre_distance = 1;
set sql = 'select chr_u, start_u, end_u, regionsize_u, chr_kb, start_kb, end_kb, regionsize_kb, ';
set sql += ' bpoverlap, centredistance from vwchrcomparesites where userdatadetailid=' + user_datadetailid
set sql += ' and bpoverlap>=' + bp_overlap
set sql += ' and kbid=' + kb_id
if chr_<>'all' and isnull(chr_,'')<>''
set @sql += ' and chr_u=''' + chr_ +''''
if (centre_distance<>'')
set sql += ' and (centredistance<=' + centre_distance + ' or ' + centre_distance + '=1) '
set sql += ' order by chr_u, start_u'
exec(sql)
end;
$BODY$
language plpgsql;
________________________________
From: Filip Rembiałkowski <plk.zuber@gmail.com>
To: Rehan Saleem <pk_rehan@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Sent: Tuesday, February 28, 2012 3:36 PM
Subject: Re: [SQL] How to convert SQL store procedure to Postgresql function
On Tue, Feb 28, 2012 at 9:50 AM, Rehan Saleem <pk_rehan@yahoo.com> wrote:
> hi ,
> how can i convert this sql store procedure to postgresql function , i shall
> be very thankful to you, as i am new to postgresql and i dont know how to
> handle this kind of store procedure in postgresql
Most people handle this with user-defined functions (UDF) written in
PL/PgSQL procedural language.
Try to read The Friendly Manual
http://www.postgresql.org/docs/current/static/sql-createfunction.html
http://www.postgresql.org/docs/current/static/plpgsql.html
Don't worry - all Transact-SQL constructs have their equivalent.
Just start rewriting your function and begin asking specific questions
here... People will help.
I would begin with
create or replace function sp_GetUserByID( in_UserId varchar(50), ...)
returns varchar(50)
language plpgsql as $$
declare
...
begin
...
return somevariable;
end;
$$;
HTH,
Filip
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to convert SQL store procedure to Postgresql function
@ 2012-02-28 12:00 Rehan Saleem <pk_rehan@yahoo.com>
parent: Filip Rembiałkowski <plk.zuber@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Rehan Saleem @ 2012-02-28 12:00 UTC (permalink / raw)
To: Filip Rembiałkowski <plk.zuber@gmail.com>; +Cc: pgsql-sql
hi ,
whats wrong with this function , i am getting syntax error which is syntax error at or near "+="
LINE 13: set sql += ' bpoverlap, centredistance from vwchrcomparesit...
^
how this problem can be solved.
thanks
CREATE OR REPLACE FUNCTION getsitesbytfid(user_datadetailid int, kb_id int,bp_overlap int,chr_ varchar ,centre_distance int)
RETURNS table(chr_u varchar,start_u int,end_u int,region_size_u int,chr_kb varchar,start_kb int ,end_kb int,region_size_kb int,bpoverlap int,centredistance int)
as
$BODY$
DECLARE sql varchar ;
BEGIN
if centre_distance= NULL THEN
set centre_distance = 1;
set sql = 'select chr_u, start_u, end_u, regionsize_u, chr_kb, start_kb, end_kb, regionsize_kb, ';
set sql += ' bpoverlap, centredistance from vwchrcomparesites where userdatadetailid=' + user_datadetailid
set sql += ' and bpoverlap>=' + bp_overlap
set sql += ' and kbid=' + kb_id
if chr_<>'all' and isnull(chr_,'')<>''
set @sql += ' and chr_u=''' + chr_ +''''
if (centre_distance<>'')
set sql += ' and (centredistance<=' + centre_distance + ' or ' + centre_distance + '=1) '
set sql += ' order by chr_u, start_u'
exec(sql)
end;
$BODY$
language plpgsql;
________________________________
From: Filip Rembiałkowski <plk.zuber@gmail.com>
To: Rehan Saleem <pk_rehan@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Sent: Tuesday, February 28, 2012 3:36 PM
Subject: Re: [SQL] How to convert SQL store procedure to Postgresql function
On Tue, Feb 28, 2012 at 9:50 AM, Rehan Saleem <pk_rehan@yahoo.com> wrote:
> hi ,
> how can i convert this sql store procedure to postgresql function , i shall
> be very thankful to you, as i am new to postgresql and i dont know how to
> handle this kind of store procedure in postgresql
Most people handle this with user-defined functions (UDF) written in
PL/PgSQL procedural language.
Try to read The Friendly Manual
http://www.postgresql.org/docs/current/static/sql-createfunction.html
http://www.postgresql.org/docs/current/static/plpgsql.html
Don't worry - all Transact-SQL constructs have their equivalent.
Just start rewriting your function and begin asking specific questions
here... People will help.
I would begin with
create or replace function sp_GetUserByID( in_UserId varchar(50), ...)
returns varchar(50)
language plpgsql as $$
declare
...
begin
...
return somevariable;
end;
$$;
HTH,
Filip
--
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] 5+ messages in thread
* Re: How to convert SQL store procedure to Postgresql function
@ 2012-02-28 12:22 Leif Biberg Kristensen <leif@solumslekt.org>
parent: Rehan Saleem <pk_rehan@yahoo.com>
0 siblings, 0 replies; 5+ messages in thread
From: Leif Biberg Kristensen @ 2012-02-28 12:22 UTC (permalink / raw)
To: pgsql-sql
Tirsdag 28. februar 2012 12.56.46 skrev Rehan Saleem :
> hi ,
> whats wrong with this function , i am getting syntax error which is syntax
> error at or near "+=" LINE 13: set sql += ' bpoverlap, centredistance
You can't concatenate that way in plpgsql. Instead of "set sql +=" try with
just "||" which is the operator joining two strings.
regards, Leif
http://code.google.com/p/yggdrasil-genealogy/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-02-28 12:22 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-28 08:50 How to convert SQL store procedure to Postgresql function Rehan Saleem <pk_rehan@yahoo.com>
2012-02-28 10:36 ` Re: How to convert SQL store procedure to Postgresql function Filip Rembiałkowski <plk.zuber@gmail.com>
2012-02-28 11:56 ` Re: How to convert SQL store procedure to Postgresql function Rehan Saleem <pk_rehan@yahoo.com>
2012-02-28 12:22 ` Re: How to convert SQL store procedure to Postgresql function Leif Biberg Kristensen <leif@solumslekt.org>
2012-02-28 12:00 ` Re: How to convert SQL store procedure to Postgresql function Rehan Saleem <pk_rehan@yahoo.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