Re: How to convert SQL store procedure to Postgresql function - Mailing list pgsql-sql

hi ,<br />whats wrong with this function , i am getting syntax error which is syntax error at or near "+="<br />LINE
13:  setsql += ' bpoverlap, centredistance from vwchrcomparesit...<br />                  ^<br />how this problem can
besolved. <br />thanks<br /><br />CREATE OR REPLACE FUNCTION getsitesbytfid(user_datadetailid int, kb_id int,bp_overlap
int,chr_varchar ,centre_distance int)<br />RETURNS table(chr_u varchar,start_u int,end_u int,region_size_u int,chr_kb
varchar,start_kbint ,end_kb int,region_size_kb int,bpoverlap int,centredistance int)<br />as<br />$BODY$<br />DECLARE
sqlvarchar ;<br />BEGIN<br /><br /><br />    if centre_distance= NULL THEN<br />    set centre_distance = 1;<br
/>    <br/>    set sql = 'select chr_u, start_u, end_u, regionsize_u, chr_kb, start_kb, end_kb, regionsize_kb, ';<br
/>    setsql += ' bpoverlap, centredistance from vwchrcomparesites    where userdatadetailid=' + user_datadetailid  <br
/>    setsql += ' and bpoverlap>=' + bp_overlap <br />    set sql += ' and kbid=' + kb_id     <br />    if
chr_<>'all'and isnull(chr_,'')<>''<br />        set @sql += ' and chr_u=''' + chr_  +''''<br />    if
(centre_distance<>'')<br />        set sql += ' and (centredistance<=' + centre_distance + ' or ' +
centre_distance+ '=1) '<br />    set sql += ' order by  chr_u, start_u'<br /><br />    exec(sql)<br />    end;<br
/>$BODY$<br/>language plpgsql;<br /><br /><br /><br /><br /><br /><div style="font-family: times new roman, new york,
times,serif; font-size: 12pt;"><div style="font-family: times new roman, new york, times, serif; font-size: 12pt;"><div
dir="ltr"><fontface="Arial" size="2"><hr size="1" /><b><span style="font-weight:bold;">From:</span></b> Filip
Rembiałkowski<plk.zuber@gmail.com><br /><b><span style="font-weight: bold;">To:</span></b> Rehan Saleem
<pk_rehan@yahoo.com><br /><b><span style="font-weight: bold;">Cc:</span></b> "pgsql-sql@postgresql.org"
<pgsql-sql@postgresql.org><br /><b><span style="font-weight: bold;">Sent:</span></b> Tuesday, February 28, 2012
3:36PM<br /><b><span style="font-weight: bold;">Subject:</span></b> Re: [SQL] How to convert SQL store procedure to
Postgresqlfunction<br /></font></div><br /> On Tue, Feb 28, 2012 at 9:50 AM, Rehan Saleem <<a
href="mailto:pk_rehan@yahoo.com"ymailto="mailto:pk_rehan@yahoo.com">pk_rehan@yahoo.com</a>> wrote:<br />> hi ,<br
/>>how can i convert this sql store procedure to postgresql function , i shall<br />> be very thankful to you, as
iam new to postgresql and i dont know how to<br />> handle this kind of store procedure in postgresql<br /><br /><br
/>Mostpeople handle this with user-defined functions (UDF) written in<br />PL/PgSQL procedural language.<br /><br />Try
toread The Friendly Manual<br />http://www.postgresql.org/docs/current/static/sql-createfunction.html<br
/>http://www.postgresql.org/docs/current/static/plpgsql.html<br/><br />Don't worry - all Transact-SQL constructs have
theirequivalent.<br /><br />Just start rewriting your function and begin asking specific questions<br />here... People
willhelp.<br /><br />I would begin with<br /><br />create or replace function sp_GetUserByID( in_UserId varchar(50),
...)<br/>returns varchar(50)<br />language plpgsql as $$<br />declare<br />...<br />begin<br /> ...<br /> return
somevariable;<br/>end;<br />$$;<br /><br />HTH,<br />Filip<br /><br /><br /></div></div>