Re: 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 <robins(dot)tharakan(at)comodo(dot)com>
> *To:* pgsql-sql(at)postgresql(dot)org
> *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
> >
>
>
>