Обсуждение: How to convert SQL store procedure to Postgresql function

On Tue, Feb 28, 2012 at 9:50 AM, Rehan Saleem <pk_rehan@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

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;

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;

 Tirsdag 28. februar 2012 12.56.46 skrev Rehan Saleem :
> hi ,
> whats wrong with this function , i am getting syntax error which is syntax
> error at or near "+=" LINE 13:  set sql += ' bpoverlap, centredistance

You can't concatenate that way in plpgsql. Instead of "set sql +=" try with 
just "||" which is the operator joining two strings.

regards, Leif
http://code.google.com/p/yggdrasil-genealogy/