hi , whats wrong with this function , i am getting syntax error which is syntax error at or near "+=" LINE 13: set sql += ' bpoverlap, centredistance from vwchrcomparesit... ^ how this problem can be solved. thanks
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,bpoverlap int,centredistance int) as $BODY$ DECLARE sql varchar ; BEGIN if centre_distance= NULL THEN set centre_distance = 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 vwchrcomparesites where userdatadetailid=' + user_datadetailid set sql += ' and bpoverlap>=' + bp_overlap set sql += ' and kbid=' + kb_id if chr_<>'all' and isnull(chr_,'')<>'' set @sql += ' and chr_u=''' + chr_ +'''' if (centre_distance<>'') set sql += ' and (centredistance<=' + centre_distance + ' or ' + centre_distance + '=1) ' set sql += ' order by chr_u, start_u' exec(sql) end; $BODY$ language plpgsql; ________________________________ From: Filip RembiaĆkowski <plk.zu...@gmail.com> To: Rehan Saleem <pk_re...@yahoo.com> Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org> Sent: Tuesday, February 28, 2012 3:36 PM Subject: Re: [SQL] How to convert SQL store procedure to Postgresql function On Tue, Feb 28, 2012 at 9:50 AM, Rehan Saleem <pk_re...@yahoo.com> wrote: > hi , > how can i convert this sql store procedure to postgresql function , i shall > be very thankful to you, as i am new to postgresql and i dont know how to > handle this kind of store procedure in postgresql Most people handle this with user-defined functions (UDF) written in PL/PgSQL procedural language. Try to read The Friendly Manual http://www.postgresql.org/docs/current/static/sql-createfunction.html http://www.postgresql.org/docs/current/static/plpgsql.html Don't worry - all Transact-SQL constructs have their equivalent. Just start rewriting your function and begin asking specific questions here... People will help. I would begin with create or replace function sp_GetUserByID( in_UserId varchar(50), ...) returns varchar(50) language plpgsql as $$ declare ... begin ... return somevariable; end; $$; HTH, Filip