[SQL] MS-SQL to PostgreSql
hi, i am trying to convert this mssql store procedure to postgresql function but it is not giving me the desired output ,this function is returning a table and you dont need to worry about what it is returning all i concern the body part of the function how to transform the ms-sql code into postgresql code, i shall be very thankful if some one convert this procedure into function, ALTER PROCEDURE [dbo].[sp_GetSitesByTFID] @UserDataDetailId varchar(50), @KBId varchar(50), @bpOverlap varchar(50), @Chr varchar(50), @CentreDistance varchar(50)='', @TotalMatched varchar(50) output AS BEGIN DECLARE @sql nvarchar(500); if (@CentreDistance='') set @CentreDistance = 1 set @sql = 'select Chr_U, Start_U, End_U, RegionSize_U, Chr_KB, Start_KB, End_KB, RegionSize_KB, ' set @sql += ' bpOverlap, CentreDistance from vwChrCompareSiteswhere UserDataDetailId=' + @UserDataDetailId set @sql += ' and bpOverlap>=' + @bpOverlap set @sql += ' AND KBId=' + @KBId if @Chr<>'All' and ISNULL(@Chr,'')<>'' set @sql += ' AND Chr_U=''' + @Chr + if (@CentreDistance<>'') set @sql += ' AND (CentreDistance<=' + @CentreDistance + ' or ' + @CentreDistance + '=1) ' set @sql += ' Order by Chr_U, Start_U' exec(@sql) set @TotalMatched = @@ROWCOUNT END
[SQL] Postgresql function with temporary tables
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
Re: [SQL] MS-SQL to PostgreSql
Hi, What all have you tried? What are you getting stuck at? Let us see some samples and may be someone could provide some input. -- Robins On 03/26/2012 01:19 PM, Rehan Saleem wrote: hi, i am trying to convert this mssql store procedure to postgresql function but it is not giving me the desired output ,this function is returning a table and you dont need to worry about what it is returning all i concern the body part of the function how to transform the ms-sql code into postgresql code, i shall be very thankful if some one convert this procedure into function, ALTER PROCEDURE [dbo].[sp_GetSitesByTFID] @UserDataDetailId varchar(50), @KBId varchar(50), @bpOverlap varchar(50), @Chr varchar(50), @CentreDistance varchar(50)='', @TotalMatched varchar(50) output AS BEGIN DECLARE @sql nvarchar(500); if (@CentreDistance='') set @CentreDistance = 1 set @sql = 'select Chr_U, Start_U, End_U, RegionSize_U, Chr_KB, Start_KB, End_KB, RegionSize_KB, ' set @sql += ' bpOverlap, CentreDistance from vwChrCompareSiteswhere UserDataDetailId=' + @UserDataDetailId set @sql += ' and bpOverlap>=' + @bpOverlap set @sql += ' AND KBId=' + @KBId if @Chr<>'All' and ISNULL(@Chr,'')<>'' set @sql += ' AND Chr_U=''' + @Chr + if (@CentreDistance<>'') set @sql += ' AND (CentreDistance<=' + @CentreDistance + ' or ' + @CentreDistance + '=1) ' set @sql += ' Order by Chr_U, Start_U' exec(@sql) set @TotalMatched = @@ROWCOUNT END smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] how to concatenate in PostgreSQL
Hi, Probably you're looking for these set of articles. http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Microsoft_SQL_Server The second article (by Ethan) has good small hints for things such as the query that you ask in this thread, when migrating from MSSQL to PostgreSQL. -- Robins On 03/24/2012 05:13 PM, Rehan Saleem wrote: hi , how can we concatinate these lines and execute sql command setsql = 'select user,username, firstname ' set sql += ' lastname, cardno from table1 where userid=' + 5 exec(sqi) where 5 is the userid from table1 thanks smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] MS-SQL to PostgreSql
Hi Rehan, p.s.: Please send all mails to list, so that all can see the response. The function returns for all, probably because you see to be RETURN'ing the values before any processing. In the MSSQL you generate the SQL in a string and then execute the SQL. However, for some reason you seem to be RETURNing the output immediately after the 'IF center_distance IS NULL' condition. Probably the RETURN needs to happen 'after' all the chr checks. -- Robins On 03/26/2012 03:48 PM, Rehan Saleem wrote: hi , i have tried this but it is not working correctly , when i pass it a value which is present in the chr column chr1 it show all chr values not only the chr1 values . CREATE OR REPLACE FUNCTION getsitesbytfid(user_datadetailid int, kb_id int,bp_overlap int,chr_ varchar ,centre_distance int) RETURNS table(chr__u varchar,start__u int,end__u int,region_size_u int,chr__kb varchar,start__kb int ,end__kb int,region_size_kb int,bp__overlap int,centre__distance int) as $BODY$ DECLARE sql varchar ; BEGIN if (centre_distance is NULL) THEN centre_distance := 1; end if; return query select chr_u, start_u, end_u, regionsize_u, chr_kb, start_kb, end_kb, regionsize_kb, bpoverlap, centredistance from vwchrcomparesites where userdatadetailid= + user_datadetailid and bpoverlap>= + bp_overlap and kbid= + kb_id ; if chr_<>'all' and COALESCE(chr_,'')<>'' then chr_:=chr_ ; end if; if centre_distance IS NULL THEN centre_distance := ' and (centredistance<=' + centre_distance + ' or ' + centre_distance + '=1) ' || ' order by chr_u, start_u'; --exec sql; end if; END; $BODY$ language plpgsql; *From:* Robins Tharakan *To:* [email protected] *Sent:* Monday, March 26, 2012 3:03 PM *Subject:* Re: [SQL] MS-SQL to PostgreSql Hi, What all have you tried? What are you getting stuck at? Let us see some samples and may be someone could provide some input. -- Robins On 03/26/2012 01:19 PM, Rehan Saleem wrote: > hi, > i am trying to convert this mssql store procedure to postgresql function > but it is not giving me the desired output ,this function is returning a > table and you dont need to worry about what it is returning all i > concern the body part of the function how to transform the ms-sql code > into postgresql code, i shall be very thankful if some one convert this > procedure into function, > > ALTER PROCEDURE [dbo].[sp_GetSitesByTFID] > @UserDataDetailId varchar(50), > @KBId varchar(50), > @bpOverlap varchar(50), > @Chr varchar(50), > @CentreDistance varchar(50)='', > @TotalMatched varchar(50) output > AS > BEGIN > > DECLARE @sql nvarchar(500); > > > if (@CentreDistance='') > set @CentreDistance = 1 > set @sql = 'select Chr_U, Start_U, End_U, RegionSize_U, Chr_KB, > Start_KB, End_KB, RegionSize_KB, ' > set @sql += ' bpOverlap, CentreDistance from vwChrCompareSiteswhere > UserDataDetailId=' + @UserDataDetailId > set @sql += ' and bpOverlap>=' + @bpOverlap > set @sql += ' AND KBId=' + @KBId > if @Chr<>'All' and ISNULL(@Chr,'')<>'' > set @sql += ' AND Chr_U=''' + @Chr + > if (@CentreDistance<>'') > set @sql += ' AND (CentreDistance<=' + @CentreDistance + ' or ' + > @CentreDistance + '=1) ' > set @sql += ' Order by Chr_U, Start_U' > > exec(@sql) > set @TotalMatched = @@ROWCOUNT > END > smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] how to concatenate in PostgreSQL
On 03/24/2012 04:43 AM, Rehan Saleem wrote: hi , how can we concatinate these lines and execute sql command In what? Psql? A PL/pgSQL function. C/Java/PHP/Python/Perl/Erlang/Lua? setsql = 'select user,username, firstname ' set sql += ' lastname, cardno from table1 where userid=' + 5 exec(sqi) where 5 is the userid from table1 thanks Cheers, Steve
