Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 91D679552D4 for ; Tue, 28 Feb 2012 07:57:03 -0400 (AST) Received: from nm33-vm6.bullet.mail.ne1.yahoo.com ([98.138.229.70]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1S2Lfx-0001d1-3S for pgsql-sql@postgresql.org; Tue, 28 Feb 2012 11:57:02 +0000 Received: from [98.138.90.51] by nm33.bullet.mail.ne1.yahoo.com with NNFMP; 28 Feb 2012 11:56:46 -0000 Received: from [98.138.88.233] by tm4.bullet.mail.ne1.yahoo.com with NNFMP; 28 Feb 2012 11:56:46 -0000 Received: from [127.0.0.1] by omp1033.mail.ne1.yahoo.com with NNFMP; 28 Feb 2012 11:56:46 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 788216.61296.bm@omp1033.mail.ne1.yahoo.com Received: (qmail 17532 invoked by uid 60001); 28 Feb 2012 11:56:46 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1330430206; bh=7RUyu+MdjDXXBcuhPzFKOPlrm+tC6zJkzl/ykiVOMps=; 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=C0gqGp5Fx4Dt5l7kU/N7totKTEGRDH7LzFZgXj29MBmsKfry2kQw8WRFsPoU4lR8LK2JDhYVPXBom+daz+Cb9p+VvH1/V8/yR0UtpXYxu3/Ggq6AcPGjDqASdTZWKS1ecmqQ9psAII35YvcW3qrOjowtNEV47DGmTsifWeZimIw= 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=AZVPtHUHddw8uRjOdhHNau48v92CKjMS5tYRnnAgl2lblDkXaueDo67EdxE6ifPiSvO+JPXF6/cr/4ef73xnbvmrFx0KBmofLAssBwM0rsIfTcS0X/pSi2iHWkkFQGryZTZX6eLQ8rOC5OD+mNbLHU2pXRKDO6bXZReHQy+ronY=; X-YMail-OSG: YNbOVlEVM1nyippbvUyzjUpwC9t8enEnLqhedglhde6CwcO 6wJGD54Y9YjyDFgRntYi9xg6nQ8SvkL4IIfycG7YcMuN4xU.xJTRVQ8oS5rM sjcIq8uFDTTrYJHDJ5sC_MhC_g1Cn3ikzb135yN0mItvIiKBcZs4HQc179uJ DngcOo601kj86lQSDfWvUan6kOtXwuItulCut4hjulSq5UDMOlGK2JNKReTV ftO266V9BbpUr4oUshbpLn0EeoiG6avRSjhochMwOqI89lk2IYcWZTHopuu7 51O6GhrqrNB51QJdq4468ZlLnAY1LiPfgcKDS4kUrAK2sWxgUz.6UyDE5ruf ZuVfqt4BMB3mR0Rd_tnnTbxlBL0mvMd6DnB2.ssm7tdpSPCaZkTywgUBh.U5 2xdl2gtbtPnzrDbhqecci6mDDGXob.9jsikYo18DS98pwRwiSJVm21_lgrdw zOD_Q1urXUozXjM35xPM9WZ.7hg2E Received: from [182.177.150.150] by web121606.mail.ne1.yahoo.com via HTTP; Tue, 28 Feb 2012 03:56:46 PST X-Mailer: YahooMailWebService/0.8.116.338427 References: <1330419004.47440.YahooMailNeo@web121602.mail.ne1.yahoo.com> Message-ID: <1330430206.16326.YahooMailNeo@web121606.mail.ne1.yahoo.com> Date: Tue, 28 Feb 2012 03:56:46 -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="1458340343-1132149226-1330430206=:16326" X-Pg-Spam-Score: -1.2 (-) X-Archive-Number: 201202/92 X-Sequence-Number: 36385 --1458340343-1132149226-1330430206=:16326 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=09i= f centre_distance=3D NULL THEN=0A=09set centre_distance =3D 1;=0A=09=0A=09s= et sql =3D 'select chr_u, start_u, end_u, regionsize_u, chr_kb, start_kb, e= nd_kb, regionsize_kb, ';=0A=09set sql +=3D ' bpoverlap, centredistance from= vwchrcomparesites=09where userdatadetailid=3D' + user_datadetailid =0A=09= set sql +=3D ' and bpoverlap>=3D' + bp_overlap =0A=09set sql +=3D ' and kbi= d=3D' + kb_id =09=0A=09if chr_<>'all' and isnull(chr_,'')<>''=0A=09=09set @= sql +=3D ' and chr_u=3D''' + chr_ +''''=0A=09if (centre_distance<>'') =0A= =09=09set sql +=3D ' and (centredistance<=3D' + centre_distance + ' or ' + = centre_distance + '=3D1) '=0A=09set sql +=3D ' order by chr_u, start_u'=0A= =0A=09exec(sql)=0A=09end;=0A$BODY$=0Alanguage plpgsql;=0A=0A=0A=0A=0A=0A=0A= =0A________________________________=0A From: Filip Rembia=C5=82kowski =0ATo: Rehan Saleem =0ACc: "pgsql-sql@= postgresql.org" =0ASent: Tuesday, February 28, 2= 012 3:36 PM=0ASubject: Re: [SQL] How to convert SQL store procedure to Post= gresql function=0A =0AOn Tue, Feb 28, 2012 at 9:50 AM, Rehan Saleem wrote:=0A> hi ,=0A> how can i convert this sql store procedur= e 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 procedu= re in postgresql=0A=0A=0AMost people handle this with user-defined function= s (UDF) written in=0APL/PgSQL procedural language.=0A=0ATry to read The Fri= endly Manual=0Ahttp://www.postgresql.org/docs/current/static/sql-createfunc= tion.html=0Ahttp://www.postgresql.org/docs/current/static/plpgsql.html=0A= =0ADon't worry - all Transact-SQL constructs have their equivalent.=0A=0AJu= st start rewriting your function and begin asking specific questions=0Ahere= ... People will help.=0A=0AI would begin with=0A=0Acreate or replace functi= on sp_GetUserByID( in_UserId varchar(50), ...)=0Areturns varchar(50)=0Alang= uage plpgsql as $$=0Adeclare=0A...=0Abegin=0A...=0Areturn somevariable;=0Ae= nd;=0A$$;=0A=0AHTH,=0AFilip --1458340343-1132149226-1330430206=:16326 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@gmai= l.com>
To: Rehan Sa= leem <pk_rehan@yahoo.com>
C= c: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql= .org>
Sent: Tuesda= y, February 28, 2012 3:36 PM
Subj= ect: Re: [SQL] How to convert SQL store procedure to Postgresql = function

=0AOn Tue, Feb 28, 2012 at 9:50 AM, Rehan Saleem <p= k_rehan@yahoo.com> wrote:
> hi ,
> how can i convert thi= s sql store procedure to postgresql function , i shall
> be very than= kful to you, as i am new to postgresql and i dont know how to
> handl= e this kind of store procedure in postgresql


Most people handle = this with user-defined functions (UDF) written in
PL/PgSQL procedural la= nguage.

Try to read The Friendly Manual
http://www.postgresql.org= /docs/current/static/sql-createfunction.html
http://www.postgresql.org/d= ocs/current/static/plpgsql.html

Don't worry - all Transact-SQL const= ructs 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 var= char(50), ...)
returns varchar(50)
language plpgsql as $$
declare
...begin
...
return somevariable;
end;
$$;

HTH,
Filip=


--1458340343-1132149226-1330430206=:16326--