[SQL] How to convert SQL store procedure to Postgresql function
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 thanks ALTERPROCEDURE [dbo].[sp_GetUserByID] @UserId varchar(50), @KBId varchar(50), @phone varchar(50), @mhr varchar(50), @Distance varchar(50)='', @Total varchar(50) output AS BEGIN DECLARE @sql nvarchar(500); if (@Distance='') set @Distance = 1 set @sql = 'select mhr_U, begin_U, End_U, areaSize_U, mhr_KB, begin_KB, End_KB, areaSize_KB, ' set @sql += ' phone, Distance from vwuserdataSites where UserId=' + @UserId set @sql += ' and phone>=' + @phone set @sql += ' AND KBId=' + @KBId if @mhr<>'All' and ISNULL(@mhr,'')<>'' set @sql += ' AND mhr_U=''' + @mhr + if (@Distance<>'') set @sql += ' AND (Distance<=' + Distance + ' or ' + Distance + '=1) ' set @sql += ' Order by mhr_U, begin_U' exec(@sql) set @Total = @@ROWCOUNT END GO
Re: [SQL] How to convert SQL store procedure to Postgresql function
On Tue, Feb 28, 2012 at 9:50 AM, Rehan Saleem 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 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SQL View to PostgreSQL View
Hi Rehan, Whilst I'm not sure what you exactly mean with ', while this is working perfectly fine in sql,..', it's kind of odd that you get result when you execute the sql. I'd suggest looking at your search_path (show search_path), which normally resolves to 'user', 'public'; If you do have a schema dbo, you could change the search_path as follows: alter user set search_path = '$user','public','dbo'; Mario [email protected] On 2012-02-26 7:50 PM, Rehan Saleem wrote: Hi , I am trying to convert sql view to postgresql view but i amgettingthe following error idontknow how to handledbo. in postgresql and when i remove dbo. from table name then view got created but it does not show any data, while this is working perfectly fine in sql, here is my code and error details CREATE OR REPLACE VIEW vwkbcomparesites as select a.kbid kb_a, b.kbid kb_b, a.chr chr_a, a.start start_a, a."end" end_a, (a."end" - a.start)+1 tagsize_a, b.chr chr_b, b.start start_b, b."end" end_b, (b."end" - b.start)+1 tagsize_b, abs((a."end" + a.start)/2 - (b."end" + b.start)/2) centredistance, case when a."end" <= b."end" and a.start >= b.start then (a."end" - a.start) when b."end" <= a."end" and b.start >= a.start then (b."end" - b.start) when a."end" <= b."end" and a.start <= b.start then (a."end" - b.start) when a."end" >= b."end" and a.start >= b.start then (b."end" - a.start) end bpoverlap from dbo.kbsites a inner join dbo.kbsites b on a.chr=b.chr inner join dbo.kbdetails kbd on a.kbid=kbd.kbid where kbd.active='1' ; i am getting this error , how can i fix this. ERROR: schema "dbo" does not exist LINE 15: from dbo.kbsites a inner join dbo.kbsites b on a.chr=b.chr ^ ** Error ** ERROR: schema "dbo" does not exist SQL state: 3F000 Character: 761
Re: [SQL] How to convert SQL store procedure to Postgresql function
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 To: Rehan Saleem Cc: "[email protected]" 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 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
Re: [SQL] How to convert SQL store procedure to Postgresql function
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 vwchrcomparesiteswhere 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 To: Rehan Saleem Cc: "[email protected]" 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 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 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to convert SQL store procedure to Postgresql function
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/ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Natural sort order
Took some time until I could try out this... But as soon I want to create the fcuntion based index it tells me: Error : ERROR: functions in index expression must be marked IMMUTABLE Deleteing the sort function and recreating with the IMMUTABLE attribute gives the same error.. Here the functions: CREATE FUNCTION btrsort_nextunit(text) RETURNS text AS $$ SELECT CASE WHEN $1 ~ '^[^0-9]+' THEN COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM '[^0-9]+'))+1 ), '' ) ELSE COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM '[0-9]+'))+1 ), '' ) END $$ LANGUAGE SQL IMMUTABLE; CREATE FUNCTION btrsort(text) RETURNS text AS $$ SELECT CASE WHEN char_length($1)>0 THEN CASE WHEN $1 ~ '^[^0-9]+' THEN RPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM '^[^0-9]+'), ''), 1, 12), 12, ' ') || btrsort(btrsort_nextunit($1)) ELSE LPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM '^[0-9]+'), ''), 1, 12), 12, ' ') || btrsort(btrsort_nextunit($1)) END ELSE $1 END ; $$ LANGUAGE SQL IMMUTABLE; And the index creation: create index port_name_btrsort_index on port(btrsort(name)); Which should speed up my query: select * from port where name not like '%Z' order by btrsort(name) asc cheers richard On Sat, 17 Dec 2011 16:16:07 +0100, Filip Rembiałkowski wrote: > If you use btrsort(column) from the example, you can just create a > functional index on this expression. > > CREATE INDEX mytable_column_btrsort_idx ON mytable( btrsort(column) ); > > this can help. > > > > > > 2011/12/17 Richard Klingler : >> Morning... >> >> What is the fastest way to achieve natural ordering from queries? >> >> I found a function at: >> http://2kan.tumblr.com/post/361326656/postgres-natural-ordering >> >> But it increases the query time from around 0.4msecs to 74msecs... >> Might be not much if occasional queries are made..but I use it for >> building >> up a hierarchical tree menu in a web application where every msecs >> counts (o; >> >> >> cheers >> richard >> >> >> -- >> Sent via pgsql-sql mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SQL View to PostgreSQL View
From: Rehan Saleem [mailto:[email protected]] Sent: Sunday, February 26, 2012 1:50 PM To: [email protected] Subject: SQL View to PostgreSQL View Hi , I am trying to convert sql view to postgresql view but i am getting the following error i dont know how to handle dbo. in postgresql and when i remove dbo. from table name then view got created but it does not show any data, while this is working perfectly fine in sql, here is my code and error details CREATE OR REPLACE VIEW vwkbcomparesites as select a.kbid kb_a, b.kbid kb_b, a.chr chr_a, a.start start_a, a."end" end_a, (a."end" - a.start)+1 tagsize_a, b.chr chr_b, b.start start_b, b."end" end_b, (b."end" - b.start)+1 tagsize_b, abs((a."end" + a.start)/2 - (b."end" + b.start)/2) centredistance, case when a."end" <= b."end" and a.start >= b.start then (a."end" - a.start) when b."end" <= a."end" and b.start >= a.start then (b."end" - b.start) when a."end" <= b."end" and a.start <= b.start then (a."end" - b.start) when a."end" >= b."end" and a.start >= b.start then (b."end" - a.start) end bpoverlap from dbo.kbsites a inner join dbo.kbsites b on a.chr=b.chr inner join dbo.kbdetails kbd on a.kbid=kbd.kbid where kbd.active='1' ; i am getting this error , how can i fix this. ERROR: schema "dbo" does not exist LINE 15: from dbo.kbsites a inner join dbo.kbsites b on a.chr=b.chr ^ ** Error ** ERROR: schema "dbo" does not exist SQL state: 3F000 Character: 761 >> First, I assume you are converting your view from SQL Server, not from SQL. SQL Server is RDBMS, while SQL is a language being used by multiple RDBMSs including PostgreSQL. Second, there is no "standard" dbo ("database owner") role in Postgres. Before converting from one RDBMS to another you need to do some basic (at least) documentation reading on "target" RDBMS (in this case - PostgreSQL). Otherwise, you will stumble on every step. Regards, Igor Neyman -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
