Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 939FC1DB08BA for ; Mon, 26 Mar 2012 07:01:25 -0300 (ADT) Received: from nm2-vm0.bullet.mail.ne1.yahoo.com ([98.138.91.39]) by makus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1SC6jq-0006am-M3 for pgsql-sql@postgresql.org; Mon, 26 Mar 2012 10:01:24 +0000 Received: from [98.138.90.49] by nm2.bullet.mail.ne1.yahoo.com with NNFMP; 26 Mar 2012 10:01:09 -0000 Received: from [98.138.89.198] by tm2.bullet.mail.ne1.yahoo.com with NNFMP; 26 Mar 2012 10:01:09 -0000 Received: from [127.0.0.1] by omp1056.mail.ne1.yahoo.com with NNFMP; 26 Mar 2012 10:01:09 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 533688.1509.bm@omp1056.mail.ne1.yahoo.com Received: (qmail 3513 invoked by uid 60001); 26 Mar 2012 10:01:09 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1332756069; bh=nevBL3vx9KvciuBSv278qGW/4PVG0PU3X9eM/te1bN4=; h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=AwmCdX95n8RPQPq1PTBnum//jFcl0IF3kIpe7cIINh4GPNL9Jm7wmveF2avapdB89DAZcLASqLc0xnwFHaxlG6n3MUG/ttGEr42Mefk10M7f8IJ0yr5deRo64dRNJqQj3R45AEE3SxrgZnyzzVgo3C9HA1a1d9j3/FeszFiEr2M= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=zTVlsG+N3gfqX+fKNlwUGOb0urO3M3bATwmrje4w/yy3zdk3P0NHznytWgjYuxOnA6ODZqeRci5Y9DlSsrjeucICGsaWK2Gx2qdH+VTPt6OlvApKfbe3qiwxGqJM4utMfQz06ATVepKDu7XbXi9Z2TgM0F5i8ukeAU5pgJDdi3s=; X-YMail-OSG: wxoO3HkVM1lJMF2fdJNhdIVWqTdsOdet0npCF8MfECsk.Lk I4ho6MqjLZA5sn_dniitg1mSOSbnW8s.iwUyNeI0zGT1pklW5eapg2YOiQ6X 0nE3Bga6qpkf3ZPf.CnrYLWuavYQGj08veVUZ9xEFY46Qri.8ZZwNuYqdMd7 OOMw1tOLNa7qhjav4MhuIXiNeN1kpJlQATwRTqzzfAXjLIDRsAPw8qhHZs1H o1tW8Yc4.wuT.2luXu6RNQOo5LgZJakOZeaCQBLncEGpApc15FI73CFvMUKv 22twyMB1W58yGzoCMA8Um9zeMBwmL1nLTAxqfs20lbRBKFt8vxoFXw0zPCZD NLMVvSI_ZtVK5Gvbii0JzCD_5rx.ASnRDlIJLAs0YtmtMfHR0QqkfrlTtgM2 U_PT5Ke1WHtmwK31jgG0LxRZxULi3snJs6E4xb0fOYBGxPXg2BpxV2RHVBgd 9EEgHjg-- Received: from [182.177.187.204] by web121606.mail.ne1.yahoo.com via HTTP; Mon, 26 Mar 2012 03:01:09 PDT X-Mailer: YahooMailWebService/0.8.117.340979 Message-ID: <1332756069.80685.YahooMailNeo@web121606.mail.ne1.yahoo.com> Date: Mon, 26 Mar 2012 03:01:09 -0700 (PDT) From: Rehan Saleem Reply-To: Rehan Saleem Subject: Postgresql function with temporary tables To: "pgsql-sql@postgresql.org" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="1458340343-991670016-1332756069=:80685" X-Pg-Spam-Score: -0.1 (/) X-Archive-Number: 201203/75 X-Sequence-Number: 36470 --1458340343-991670016-1332756069=:80685 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable hi everyone ,=0Ai have converted a ms-sql store procedure into=A0PostgreSQL= =A0function , but my=A0PostgreSQL=A0function is not giving the same output = as my ms-sql procedure does, in this ms-sql store procedure putting the res= ult of 2 queries in two temporary tables and displaying those column values= which are same in both temporary tables . i am sending you both ms-sql pro= cedure which i tried to convert in=A0PostgreSQL=A0and my converted=A0Postgr= eSQL=A0function , please correct my code where you find any mistakes. i sha= ll be very thankful to you.=0A=0AMS-SQL STORE PROCEDURE=A0=0AALTER Proc [db= o].[sp_GetAllCommonOverlapSites]=0A@bpOverlap INT,=A0=0A@CentreDistance var= char(50),=0A@UserDataDetailId INT,=0A@TotalMatched varchar(50) output=0A=A0= AS=0A--RUN THIS TO CREATE TABLE-VALUED PARAMETERS TYPE=0A--CREATE TYPE dbo.= TFdetailsID_type AS TABLE =A0 (Id int NOT NULL PRIMARY KEY)=A0=0Aif object_= id('tempdb..#tblTFSites1') is not null=0A=A0 drop table tempdb..#tblTFSites= 1=0Aif object_id('tempdb..#tblTFSites2') is not null=0A=A0 drop table tempd= b..#tblTFSites2=0A=0Acreate table #tblTFSites1(=0A Chr varchar(50) NULL,=0A= Start int NULL,=0A [End] int NULL=0A)=0A=0Acreate table #tblTFSites2(=0A C= hr varchar(50) NULL,=0A Start int NULL,=0A [End] int NULL=0A)=0Aif (@Centre= Distance=3D'')=0Aset @CentreDistance =3D 1=0ADECLARE @FirstRun int, @ID int= =0ASET @FirstRun=3D1=0A=0A=A0DECLARE c_TFDetailsID CURSOR FOR SELECT KBId f= rom KBDetails where Active=3D1=0A=A0OPEN c_TFDetailsID=0A=A0FETCH NEXT FROM= c_TFDetailsID INTO @ID=0A=A0WHILE (@@FETCH_STATUS =3D 0) BEGIN=0A--print @= ID=0AIF (@FirstRun=3D1) BEGIN=0AINSERT INTO #tblTFSites1 (Chr, Start,[End])= =0ASelect Chr_U, Start_U, End_U from vwChrCompareSites where KBId=3D@ID and= UserDataDetailId=3D@UserDataDetailId=A0=0Aand bpOverlap >=3D @bpOverlap an= d (CentreDistance <=3D @CentreDistance or @CentreDistance=3D1)=0ASET @First= Run=3D0=A0=0AEND=0AELSE =A0BEGIN=0AINSERT INTO #tblTFSites2 (Chr, Start,[En= d])=0Aselect A.Chr, A.start, A.[end]=0Afrom =A0KBSites KB inner join #tblTF= Sites1 A on KB.Chr COLLATE DATABASE_DEFAULT =3D A.Chr=0Awhere KBId=3D@ID=0A= AND=0A@bpOverlap <=3D CASE=A0=0AWHEN A.[end] <=3D KB.[END] AND A.Start >=3D= KB.Start =A0=0ATHEN (A.[End] - A.Start)=A0=0AWHEN KB.[end] <=3D A.[END] AN= D KB.Start >=3D A.Start =A0=0ATHEN (KB.[End] - KB.Start)=A0=0AWHEN A.[end] = <=3D KB.[END] AND A.Start <=3D KB.Start =A0=0ATHEN (A.[End] - KB.Start)=A0= =0AWHEN A.[end] >=3D KB.[END] AND A.Start >=3D KB.Start =A0=A0=0ATHEN (KB.[= End] - A.Start) =A0=0AEND=A0=0Atruncate table #tblTFSites1=0AINSERT INTO #t= blTFSites1 (Chr, Start,[End]) SELECT Chr, Start,[End] FROM #tblTFSites2=0AE= ND=0A=A0FETCH NEXT FROM c_TFDetailsID INTO @ID=0AEnd --end of while loop=0A= CLOSE c_TFDetailsID=0ADEALLOCATE c_TFDetailsID=0A=0ASelect Chr Chr_U, Start= Start_U, [End] End_U from #tblTFSites1=0Aset @TotalMatched =3D @@ROWCOUNT= =0A=0AGO=0A=0A=0AMY CONVERTED POSTGRESQL FUNCTION=A0=0ACREATE OR REPLACE FU= NCTION getallcommonoverlapsites(user_datadetailid int , bp_overlap int ,cen= tre_distance int )=0ARETURNS table(chr__u varchar,start__u int , "end__u" i= nt)=0Aas=0A$BODY$=0ADECLARE id_ int;=0ADECLARE first_run boolean;=0ADECLARE= totalmached int;=0ADECLARE c_tfdetailsid CURSOR FOR SELECT kbid from kbdet= ails where active =3D'1';=0A=A0=0ABEGIN=0ADROP TABLE IF EXISTS tbltfsites1;= =0ADROP TABLE IF EXISTS tbltfsites2;=0ACREATE TEMP TABLE tbltfsites1=A0=0A(= =0Achr varchar ,=0Astart int,=0A"end" int=0A) ;=0ACREATE TEMP TABLE tbltfsi= tes2=0A(=0A=0Achr varchar,=0Astart int,=0A"end" int=0A);=0A=0Aif centre_dis= tance IS NULL THEN=0Acentre_distance :=3D 1;=0Aend if;=0A=0Afirst_run :=3Dt= rue;=0A=0AOPEN c_tfdetailsid;=0AFETCH NEXT FROM c_tfdetailsid INTO id_;=0A= =A0 =A0 =A0 =A0 =A0=A0=0Aif first_run =3Dtrue THEN=0Ainsert into tbltfsites= 1 (chr, start,"end")=0Aselect chr_u, start_u, end_u from vwchrcomparesites = where kbid=3Did_ and userdatadetailid=3Duser_datadetailid=A0=0Aand bpoverla= p >=3D bp_overlap and (centredistance <=3D centre_distance or centre_distan= ce=3D1);=0Afirst_run:=3DFalse;=0A=A0=0A=A0 =A0 =A0 =A0else=0A=A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 insert into tbltfsites2 (chr, start,"end")=0Aselect a.c= hr, a.start, a."end"=0Afrom =A0kbsites kb inner join tbltfsites1 a on kb.ch= r =3D a.chr=0Awhere kbid=3Did_ and=0Acase=A0=0Awhen a."end" <=3D kb."end" a= nd a.start >=3D kb.start =A0=0Athen (a."end" - a.start)=A0=0Awhen kb."end" = <=3D a."end" and kb.start >=3D a.start =A0=A0=0Athen (kb."end" - kb.start)= =A0=0Awhen a."end" <=3D kb."end" and a.start <=3D kb.start =A0 =A0=0Athen (= a."end" - kb.start)=A0=0Awhen a."end" >=3D kb."end" and a.start >=3D kb.sta= rt =A0 =A0=0Athen (kb."end" - a.start) =A0=0Aend <=3D bp_overlap =A0;=0Atru= ncate table tbltfsites1;=0Ainsert into tbltfsites1 (chr, start,"end") selec= t chr, start,"end" from tbltfsites2;=0A =A0 =A0end if;=0Aexit when c_tfdeta= ilsid is null;=0Aclose c_tfdetailsid;=0A=A0 =A0 =A0 =A0 return query select= chr , start , "end" from tbltfsites1 ;=0A=A0 =A0 =A0 =A0=0Aend;=0A$BODY$= =0ALANGUAGE plpgsql;=0A=0A=0A=0A=0A=0Aregards=0ARehan Saleem --1458340343-991670016-1332756069=:80685 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
hi ev= eryone ,
i have converted a ms-sql store procedure into PostgreSQL<= font face=3D"'times new roman', 'new york', times, serif" size=3D"3"> = function , but my PostgreSQL function is not giving the same outp= ut as my ms-sql procedure does, in this ms-sql store procedure putting the = result of 2 queries in two temporary tables and displaying those column val= ues which are same in both temporary tables . i am sending you both ms-sql = procedure which i tried to convert in PostgreSQL and my converted=  PostgreSQL function , please correct my code where you find any mistakes. i shall be very thankful to you.
MS-SQL STORE PROCEDURE 
ALTER Proc [dbo].[sp_Get= AllCommonOverlapSites]
=09@bpOverlap INT, 
=09@CentreDistance varchar(50),
=09@UserDataDetailId INT,=
=09@TotalMatched= varchar(50) output
 AS
--RUN THIS TO CREATE TABLE-VALUED PARAMETERS= TYPE
--CREATE TYPE dbo.TFdetailsID_type AS TABLE   (Id int NOT NULL = PRIMARY KEY) 
if object_id('tempdb..#tblTFSites1') is not null=
 = ; drop table tempdb..#tblTFSites1
if object_id('tempdb..#tblTFSites2') is = not null
  drop table tempdb..#tblTFSites2

create table #tblTFSites1(<= /div>
=09 Chr varchar(5= 0) NULL,
=09 Start int NULL,
=09 [End] int NULL
)

create table #tblTFSites2(
=09 Chr varchar(50) NULL,
=09 Start int NULL,
=09 [End] int N= ULL
)
if (@CentreDistance=3D'')
=09set @CentreDistance =3D 1
DECLARE @FirstRun int= , @ID int
SET @FirstRun=3D1

 DECLARE c_TFDetailsID CURSOR FOR SELECT KBId from KBDet= ails where Active=3D1
 OPEN c_TFDetailsID
 FETCH NEXT FROM c_TF= DetailsID INTO @ID
 WHILE (@@FETCH_STATUS =3D 0) BEGIN
=09--print @ID<= /div>
=09IF (@FirstRun= =3D1) BEGIN
=09= =09INSERT INTO #tblTFSites1 (Chr, Start,[End])
=09=09Select Chr_U, Start_U, End_U from vwChrCompareSites where K= BId=3D@ID and UserDataDetailId=3D@UserDataDetailId 
<= font face=3D"'times new roman', 'new york', times, serif">=09=09=09and bpOverlap >= =3D @bpOverlap and (CentreDistance <=3D @CentreDistance or @CentreDistan= ce=3D1)
=09=09SET @FirstRun=3D0 
=09END
=09ELSE  BEGIN
=09= =09INSERT INTO #tblTFSites2 (Chr, Start,[End])
=09=09select A.Chr, A.start, A.= [end]
=09=09from  KBSites KB inner join #tblTFSites1 A on KB.Chr COLLATE DATABAS= E_DEFAULT =3D A.Chr
=09=09where KBId=3D@ID
=09=09AND
=09=09@bpOverlap= <=3D CASE 
=09=09=09WHEN A.[end] <=3D KB.[END] AND A.Start >=3D KB.Sta= rt  
=09=09= =09=09THEN (A.[End] - A.Start) 
=09=09=09WHEN KB.[end] <=3D A.[END] AN= D KB.Start >=3D A.Start  
=09=09=09=09THEN (KB.[End] - KB.Start) =
=09=09=09WHEN A.[end] <=3D KB.[END] AND A.Start <=3D KB.Start  
<= div>=09=09=09=09THEN (A.[E= nd] - KB.Start) 
=09=09=09WHEN A.[end] >=3D KB.[END] AND A.Start >=3D KB.S= tart   
=09=09=09=09THEN (KB.[End] - A.Start)  
=09=09=09END 
<= div>=09=09truncate table #= tblTFSites1
=09=09INSERT INTO #tblTFSites1 (Chr, Start,[End]) SELECT Chr, St= art,[End] FROM #tblTFSites2
=09END=09=09
=09
End --end of= while loop=09=09<= /span>
=09=
CLOSE c_TFDetailsID
DEALLOCATE c_TFDeta= ilsID

Select Chr Chr_U, Start Start_U, [End] End_U from #tblTFSites1=

GO


<= font face=3D"'times new roman', 'new york', times, serif">MY CONVERTED POST= GRESQL FUNCTION 
CREATE OR REPLACE FUNCTION getallcommonoverlapsites(user_datad= etailid int , bp_overlap int ,centre_distance int )
RETURNS table= (chr__u varchar,start__u int , "end__u" int)
as
$BODY$<= /div>
DECLARE id_ int;
DECLARE first_run boolean;
D= ECLARE totalmached int;
DECLARE c_tfdetailsid CURSOR FOR SELECT k= bid from kbdetails where active =3D'1';
 
BEGIN
DROP TABLE IF EXISTS tbltfsites1;
DROP TABLE IF EXISTS tb= ltfsites2;
CREATE TEMP TABLE tbltfsites1 
=09
=09(
<= span class=3D"Apple-tab-span" style=3D"white-space:pre">=09chr varch= ar ,
=09= start int,
=09"end" int
=09= ) ;
=09
CREATE TEMP TABLE tbltfsites2
=09(

=09= chr varchar,
=09start int,
=09"end" int
=09);

=09if centre_dista= nce IS NULL THEN
=09centre_distance :=3D 1;
=09end if;

=09first_run :=3Dtrue;

OPEN c_tfdetailsid;
FETCH NEXT FROM c_tfdetailsid INTO id_= ;=09
<= div>          
=09if first_run =3Dtrue THEN
=09=
=09=09<= /span>insert into tbltfsites1 (chr, start,"end")
=09=09select chr_u, start_u= , end_u from vwchrcomparesites where kbid=3Did_ and userdatadetailid=3Duser= _datadetailid 
=09=09and bpoverlap >=3D bp_overlap and (centredistan= ce <=3D centre_distance or centre_distance=3D1);
=09
=09first_run:=3DFalse;
=09 
      &n= bsp;else
                = insert into tbltfsites2 (chr, start,"end")
=09=09select a.chr, a.start, a."e= nd"
=09= =09from  kbsites kb inner join tbltfsites1 a on kb.chr =3D a.ch= r
=09=09= where kbid=3Did_ and
=09=09
=09=09case 
=09=09=09when a."end" <= ;=3D kb."end" and a.start >=3D kb.start  
=09=09=09=09then= (a."end" - a.start) 
=09=09=09when kb."end" <=3D a."end" and kb.s= tart >=3D a.start   
=09=09=09=09then (kb."end" - kb.start)&nbs= p;
=09= =09=09when a."end" <=3D kb."end" and a.start <=3D kb.start &nb= sp;  
=09=09=09=09then (a."end" - kb.start) 
=09=09=09when a."end" = >=3D kb."end" and a.start >=3D kb.start    
=09=09=09=09then= (kb."end" - a.start)  
=09=09end <=3D bp_overlap  ;
= =09=09truncate t= able tbltfsites1;
=09=09insert into tbltfsites1 (chr, start,"end") select ch= r, start,"end" from tbltfsites2;
=09=09
=09    end if;
=09exit when = c_tfdetailsid is null;
=09close c_tfdetailsid;
     = ;   return query select chr , start , "end" from tbltfsites1 ;
       
end;
$BODY$
LA= NGUAGE plpgsql;



=

regards
Rehan Saleem

<= div>
<= /div>

=




--1458340343-991670016-1332756069=:80685--