Skip site navigation (1) Skip section navigation (2)

Postgresql function with temporary tables

From: Rehan Saleem <pk_rehan(at)yahoo(dot)com>
To: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Postgresql function with temporary tables
Date: 2012-03-26 10:01:09
Message-ID: 1332756069.80685.YahooMailNeo@web121606.mail.ne1.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-sql
hi everyone ,
i have converted a ms-sql store procedure into PostgreSQL function , but my PostgreSQL function is not giving the same output 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 values 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_GetAllCommonOverlapSites]
@bpOverlap INT, 
@CentreDistance varchar(50),
@UserDataDetailId INT,
@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(
 Chr varchar(50) NULL,
 Start int NULL,
 [End] int NULL
)

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

 DECLARE c_TFDetailsID CURSOR FOR SELECT KBId from KBDetails where Active=1
 OPEN c_TFDetailsID
 FETCH NEXT FROM c_TFDetailsID INTO @ID
 WHILE (@@FETCH_STATUS = 0) BEGIN
--print @ID
IF (@FirstRun=1) BEGIN
INSERT INTO #tblTFSites1 (Chr, Start,[End])
Select Chr_U, Start_U, End_U from vwChrCompareSites where KBId=(at)ID and UserDataDetailId=(at)UserDataDetailId 
and bpOverlap >= @bpOverlap and (CentreDistance <= @CentreDistance or @CentreDistance=1)
SET @FirstRun=0 
END
ELSE  BEGIN
INSERT INTO #tblTFSites2 (Chr, Start,[End])
select A.Chr, A.start, A.[end]
from  KBSites KB inner join #tblTFSites1 A on KB.Chr COLLATE DATABASE_DEFAULT = A.Chr
where KBId=(at)ID
AND
@bpOverlap <= CASE 
WHEN A.[end] <= KB.[END] AND A.Start >= KB.Start  
THEN (A.[End] - A.Start) 
WHEN KB.[end] <= A.[END] AND KB.Start >= A.Start  
THEN (KB.[End] - KB.Start) 
WHEN A.[end] <= KB.[END] AND A.Start <= KB.Start  
THEN (A.[End] - KB.Start) 
WHEN A.[end] >= KB.[END] AND A.Start >= KB.Start   
THEN (KB.[End] - A.Start)  
END 
truncate table #tblTFSites1
INSERT INTO #tblTFSites1 (Chr, Start,[End]) SELECT Chr, Start,[End] FROM #tblTFSites2
END
 FETCH NEXT FROM c_TFDetailsID INTO @ID
End --end of while loop
CLOSE c_TFDetailsID
DEALLOCATE c_TFDetailsID

Select Chr Chr_U, Start Start_U, [End] End_U from #tblTFSites1
set @TotalMatched = @@ROWCOUNT

GO


MY CONVERTED POSTGRESQL FUNCTION 
CREATE OR REPLACE FUNCTION getallcommonoverlapsites(user_datadetailid int , bp_overlap int ,centre_distance int )
RETURNS table(chr__u varchar,start__u int , "end__u" int)
as
$BODY$
DECLARE id_ int;
DECLARE first_run boolean;
DECLARE totalmached int;
DECLARE c_tfdetailsid CURSOR FOR SELECT kbid from kbdetails where active ='1';
 
BEGIN
DROP TABLE IF EXISTS tbltfsites1;
DROP TABLE IF EXISTS tbltfsites2;
CREATE TEMP TABLE tbltfsites1 
(
chr varchar ,
start int,
"end" int
) ;
CREATE TEMP TABLE tbltfsites2
(

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

if centre_distance IS NULL THEN
centre_distance := 1;
end if;

first_run :=true;

OPEN c_tfdetailsid;
FETCH NEXT FROM c_tfdetailsid INTO id_;
          
if first_run =true THEN
insert into tbltfsites1 (chr, start,"end")
select chr_u, start_u, end_u from vwchrcomparesites where kbid=id_ and userdatadetailid=user_datadetailid 
and bpoverlap >= bp_overlap and (centredistance <= centre_distance or centre_distance=1);
first_run:=False;
 
       else
                insert into tbltfsites2 (chr, start,"end")
select a.chr, a.start, a."end"
from  kbsites kb inner join tbltfsites1 a on kb.chr = a.chr
where kbid=id_ and
case 
when a."end" <= kb."end" and a.start >= kb.start  
then (a."end" - a.start) 
when kb."end" <= a."end" and kb.start >= a.start   
then (kb."end" - kb.start) 
when a."end" <= kb."end" and a.start <= kb.start    
then (a."end" - kb.start) 
when a."end" >= kb."end" and a.start >= kb.start    
then (kb."end" - a.start)  
end <= bp_overlap  ;
truncate table tbltfsites1;
insert into tbltfsites1 (chr, start,"end") select chr, start,"end" from tbltfsites2;
    end if;
exit when c_tfdetailsid is null;
close c_tfdetailsid;
        return query select chr , start , "end" from tbltfsites1 ;
       
end;
$BODY$
LANGUAGE plpgsql;





regards
Rehan Saleem

pgsql-sql by date

Next:From: Robins TharakanDate: 2012-03-26 10:03:26
Subject: Re: MS-SQL to PostgreSql
Previous:From: Rehan SaleemDate: 2012-03-26 07:49:07
Subject: MS-SQL to PostgreSql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group