From: Rehan Saleem <pk_rehan@yahoo.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Postgresql function with temporary tables
Date: Mon, 26 Mar 2012 03:01:09 -0700 (PDT)
Message-ID: <1332756069.80685.YahooMailNeo@web121606.mail.ne1.yahoo.com> (raw)
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=@ID and UserDataDetailId=@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=@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pk_rehan@yahoo.com
Subject: Re: Postgresql function with temporary tables
In-Reply-To: <1332756069.80685.YahooMailNeo@web121606.mail.ne1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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