Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 2489B9552D4 for ; Tue, 28 Feb 2012 08:00:20 -0400 (AST) Received: from nm35-vm6.bullet.mail.ne1.yahoo.com ([98.138.229.102]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1S2Lj7-0001eI-5d for pgsql-sql@postgresql.org; Tue, 28 Feb 2012 12:00:19 +0000 Received: from [98.138.90.49] by nm35.bullet.mail.ne1.yahoo.com with NNFMP; 28 Feb 2012 12:00:03 -0000 Received: from [98.138.88.232] by tm2.bullet.mail.ne1.yahoo.com with NNFMP; 28 Feb 2012 12:00:03 -0000 Received: from [127.0.0.1] by omp1032.mail.ne1.yahoo.com with NNFMP; 28 Feb 2012 12:00:03 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 166566.50507.bm@omp1032.mail.ne1.yahoo.com Received: (qmail 94202 invoked by uid 60001); 28 Feb 2012 12:00:03 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1330430402; bh=g5be86omxenMm9/JGrrndlzCWcemD5HmHbPtTE+m2bE=; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=6ZdSmbIyWIYGDfAI3t+AhdGGsvvWV+RulVHOSb9J+Vi7vkMl5qRQBTcCmAYY4LPRzkQ7LYLFNR+6Y5IEUghb6oRMXcyQOQG+LCPdDIVz6FAI2VToEvVN9c6gYmbybT5gF8KknB8hpI3vg5Rhf06VOOhsHJCbWsoaz5UUloFBswE= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type; b=XjB6gJv12f50yFk+inSJeLAD2UNDxKhdEbKrkzznebgoI//Y+sgoTGCdP7qz10OCRKTw209TeK3ivn3YHJavddLxabSf981eRcrruf0Ss48UHQF+e6x+Nl6WqHt/FX6J2b6K0AYZX3DgNW+fyPTJB9g7DgajSP3GnKdqkZLQ3OE=; X-YMail-OSG: DAI1T70VM1lzSk7k6JhhudzA7Bx3Uhe1asaF0GIwWt53v2a 3oadyMR9OwQ7kEp77cY3NGjaqQoU39ssPfFOMbo4vcnefwUo4fivypxwV7OS FzCxJ5AV.xiw_Ye1_2ylIVT1PfOlKowOX1H9xOAGzfo1wcL55nPazUJFe5T1 O7BbB_RmJLDlfaHt9OaaDe.IqCH3IJU6YOF4XCqh8PqOqjf.C.iu7sbtnW86 uS1RSJvZFESXPtAaLk45108BDU1CNNPGGUQtTou.3VWALloQx_ilZxZDjjUJ k.0XQiwAFw2i4YKE0gpTvm_FzUXl4rju7v8cudMKAippt55KLhRYoPkvWlTe psTHdoU8fD8WoPNFN4vvAbWFgFLBDf7OdFh3pi7sYzzbjhJs47b7kWA56bUN fdBQoFEPUU_WolieDMzUr4ipV31c2rxjI4M.Z0o3jS5JpbDC3NZZfEsBvNyN vwYfggrNskKYrffa_NXnA.gGeWNhj Received: from [182.177.150.150] by web121602.mail.ne1.yahoo.com via HTTP; Tue, 28 Feb 2012 04:00:02 PST X-Mailer: YahooMailWebService/0.8.116.338427 References: <1330419004.47440.YahooMailNeo@web121602.mail.ne1.yahoo.com> Message-ID: <1330430402.58997.YahooMailNeo@web121602.mail.ne1.yahoo.com> Date: Tue, 28 Feb 2012 04:00:02 -0800 (PST) From: Rehan Saleem Reply-To: Rehan Saleem Subject: Re: How to convert SQL store procedure to Postgresql function To: =?utf-8?B?RmlsaXAgUmVtYmlhxYJrb3dza2k=?= Cc: "pgsql-sql@postgresql.org" In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="-2038480821-401425961-1330430402=:58997" X-Pg-Spam-Score: -1.2 (-) X-Archive-Number: 201202/93 X-Sequence-Number: 36386 ---2038480821-401425961-1330430402=:58997 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable hi ,=0Awhats wrong with this function , i am getting syntax error which is = syntax error at or near "+=3D"=0ALINE 13: set sql +=3D ' bpoverlap, centre= distance from vwchrcomparesit...=0A ^=0Ahow this problem c= an be solved. =0Athanks=0A=0ACREATE OR REPLACE FUNCTION getsitesbytfid(user= _datadetailid int, kb_id int,bp_overlap int,chr_ varchar ,centre_distance i= nt)=0ARETURNS table(chr_u varchar,start_u int,end_u int,region_size_u int,c= hr_kb varchar,start_kb int ,end_kb int,region_size_kb int,bpoverlap int,cen= tredistance int)=0Aas=0A$BODY$=0ADECLARE sql varchar ;=0ABEGIN=0A=0A=0A = if centre_distance=3D NULL THEN=0A set centre_distance =3D 1;=0A =0A = set sql =3D 'select chr_u, start_u, end_u, regionsize_u, chr_kb, start_k= b, end_kb, regionsize_kb, ';=0A set sql +=3D ' bpoverlap, centredistance= from vwchrcomparesites where userdatadetailid=3D' + user_datadetailid = =0A set sql +=3D ' and bpoverlap>=3D' + bp_overlap =0A set sql +=3D '= and kbid=3D' + kb_id =0A if chr_<>'all' and isnull(chr_,'')<>''=0A = set @sql +=3D ' and chr_u=3D''' + chr_ +''''=0A if (centre_dista= nce<>'') =0A set sql +=3D ' and (centredistance<=3D' + centre_distan= ce + ' or ' + centre_distance + '=3D1) '=0A set sql +=3D ' order by chr= _u, start_u'=0A=0A exec(sql)=0A end;=0A$BODY$=0Alanguage plpgsql;=0A= =0A=0A=0A________________________________=0A From: Filip Rembia=C5=82kowski= =0ATo: Rehan Saleem =0ACc: "pgsq= l-sql@postgresql.org" =0ASent: Tuesday, February= 28, 2012 3:36 PM=0ASubject: Re: [SQL] How to convert SQL store procedure t= o Postgresql function=0A =0AOn Tue, Feb 28, 2012 at 9:50 AM, Rehan Saleem <= pk_rehan@yahoo.com> wrote:=0A> hi ,=0A> how can i convert this sql store pr= ocedure to postgresql function , i shall=0A> be very thankful to you, as i = am new to postgresql and i dont know how to=0A> handle this kind of store p= rocedure in postgresql=0A=0A=0AMost people handle this with user-defined fu= nctions (UDF) written in=0APL/PgSQL procedural language.=0A=0ATry to read T= he Friendly Manual=0Ahttp://www.postgresql.org/docs/current/static/sql-crea= tefunction.html=0Ahttp://www.postgresql.org/docs/current/static/plpgsql.htm= l=0A=0ADon't worry - all Transact-SQL constructs have their equivalent.=0A= =0AJust start rewriting your function and begin asking specific questions= =0Ahere... People will help.=0A=0AI would begin with=0A=0Acreate or replace= function sp_GetUserByID( in_UserId varchar(50), ...)=0Areturns varchar(50)= =0Alanguage plpgsql as $$=0Adeclare=0A...=0Abegin=0A...=0Areturn somevariab= le;=0Aend;=0A$$;=0A=0AHTH,=0AFilip=0A=0A-- =0ASent via pgsql-sql mailing li= st (pgsql-sql@postgresql.org)=0ATo make changes to your subscription:=0Ahtt= p://www.postgresql.org/mailpref/pgsql-sql ---2038480821-401425961-1330430402=:58997 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable hi ,
whats wrong with this function , i am getting syntax error which = is syntax error at or near "+=3D"
LINE 13:  set sq= l +=3D ' bpoverlap, centredistance from vwchrcomparesit...
 &= nbsp;           &nbs= p;    ^
how this problem can be solved.
than= ks

CREATE OR REPLACE FUNCTION getsitesbytfid(user_datadetailid i= nt, kb_id int,bp_overlap int,chr_ varchar ,centre_distance int)
RETURN= S table(chr_u varchar,start_u int,end_u int,region_size_u int,chr_kb varcha= r,start_kb int ,end_kb int,region_size_kb int,bpoverlap int,centredistance = int)
as
$BODY$
DECLARE sql varchar ;
BEGIN

<= br />    if centre_distance=3D NULL THEN
 &nb= sp;  set centre_distance =3D 1;
    
    set sql =3D 'select chr_u, start_u, end_u, regionsize_u, chr_kb, start_kb, end_kb, regionsize_kb, ';
 &= nbsp;  set sql +=3D ' bpoverlap, centredistance from vwchrcom= paresites    where userdatadetailid=3D' + user_data= detailid  
    set sql +=3D ' and bp= overlap>=3D' + bp_overlap
    set sql +=3D= ' and kbid=3D' + kb_id    
  &= nbsp; if chr_<>'all' and isnull(chr_,'')<>= ''
        set @sql += =3D ' and chr_u=3D''' + chr_  +'''= 9;
    if (centre_distance<>'')
        set sql +=3D ' and (c= entredistance<=3D' + centre_distance + ' or ' + centre_dista= nce + '=3D1) '
    set sql +=3D ' orde= r by  chr_u, start_u'

    exec(= sql)
    end;
$BODY$
language plpgsql;
From: Filip Rembia=C5=82kowski <plk.zuber@gmail.com>
To: Rehan Saleem <pk_rehan@yahoo.= com>
Cc: "pgsq= l-sql@postgresql.org" <pgsql-sql@postgresql.org>
Sent: Tuesday, February 28, 2012 3:3= 6 PM
Subject: Re: [SQL= ] How to convert SQL store procedure to Postgresql function
=0AOn Tue, Feb 28, 2012 at 9:50 AM, Rehan Saleem <pk_rehan@yaho= o.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 us= er-defined functions (UDF) written in
PL/PgSQL procedural language.
<= BR>Try to read The Friendly Manual
http://www.postgresql.org/docs/curren= t/static/sql-createfunction.html
http://www.postgresql.org/docs/current/= static/plpgsql.html

Don't worry - all Transact-SQL constructs have t= heir equivalent.

Just start rewriting your function and begin asking= specific questions
here... People will help.

I would begin with<= BR>
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@post= gresql.org)
To make changes to your subscription:
http://www.post= gresql.org/mailpref/pgsql-sql


---2038480821-401425961-1330430402=:58997--