Thread: Migration from DB2 to PostgreSQL
While migrating my application from DB2 to PostgreSQL. I want to migrate following functions in PostgreSQL. TO_CHAR() in DB2 which can take three arguments as follows: SELECT TO_CHAR(CURRENT_DATE,'YYYY-MM-DD',112.50) FROM SYSIBM.SYSDUMMY1 I am not sure what is the purpose of third argument in TO_CHAR() function of DB2. I think we can create one user define function with name TO_CHAR which can take three argument like DB2. I want to know that if we want to create user define function TO_CHAR(). What should be the data type of third argument? which can handle maximum data types and more suitable for real time scenarios. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-tp5759820.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
sachin kotwal wrote: > While migrating my application from DB2 to PostgreSQL. > > I want to migrate following functions in PostgreSQL. > > TO_CHAR() in DB2 which can take three arguments as follows: > > SELECT TO_CHAR(CURRENT_DATE,'YYYY-MM-DD',112.50) FROM SYSIBM.SYSDUMMY1 > > I am not sure what is the purpose of third argument in TO_CHAR() function of > DB2. > > I think we can create one user define function with name TO_CHAR which can > take three argument like DB2. > > I want to know that if we want to create user define function TO_CHAR(). > What should be the data type of third argument? > > which can handle maximum data types and more suitable for real time > scenarios. Look at IBM's documentation at http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0007110.html TO_CHAR is a synonym for VARCHAR_FORMAT. The third argument is an optional locale name, like 'en_US', which will be used to generate the text parts of the format you chose. For a format like 'YYYY-MM-DD' this has no effect at all and can be omitted. PostgreSQL has no such capability. Unless you need that and want to code it yourself, the best solution would be to write a function that just ignores the third argument. Yours, Laurenz Albe
On Wed, Jun 19, 2013 at 6:00 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > sachin kotwal wrote: >> While migrating my application from DB2 to PostgreSQL. >> >> I want to migrate following functions in PostgreSQL. >> >> TO_CHAR() in DB2 which can take three arguments as follows: >> >> SELECT TO_CHAR(CURRENT_DATE,'YYYY-MM-DD',112.50) FROM SYSIBM.SYSDUMMY1 >> >> I am not sure what is the purpose of third argument in TO_CHAR() function of >> DB2. >> >> I think we can create one user define function with name TO_CHAR which can >> take three argument like DB2. >> >> I want to know that if we want to create user define function TO_CHAR(). >> What should be the data type of third argument? >> >> which can handle maximum data types and more suitable for real time >> scenarios. > > Look at IBM's documentation at > http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0007110.html > > TO_CHAR is a synonym for VARCHAR_FORMAT. > The third argument is an optional locale name, like 'en_US', > which will be used to generate the text parts of the > format you chose. > > For a format like 'YYYY-MM-DD' this has no effect at all > and can be omitted. > > PostgreSQL has no such capability. Unless you need that and > want to code it yourself, the best solution would be to > write a function that just ignores the third argument. > Just wondering: If this particular function is to be used repeatedly in a single query, would the cost of having a wrapper function around the original function be too large? For example, if this function appears in a WHERE clause against a table containing millions of rows. -- Amit Langote
On Thu, Jun 20, 2013 at 11:10 AM, Amit Langote <amitlangote09@gmail.com> wrote: > If this particular function is to be used repeatedly in a single > query, would the cost of having a wrapper function around the original > function be too large? For example, if this function appears in a > WHERE clause against a table containing millions of rows. If your wrapper function is written in SQL and is trivial (eg ignore the third parameter and pass the other two on), the planner should be able to optimize right through it. Best way to find out is with EXPLAIN, which I've been using a good bit lately. The optimizer's pretty smart. ChrisA
Hi,
On Thu, Jun 20, 2013 at 10:27 AM, Chris Angelico <rosuav@gmail.com> wrote:
> On Thu, Jun 20, 2013 at 11:10 AM, Amit Langote <amitlangote09@gmail.com> wrote:
>> If this particular function is to be used repeatedly in a single
>> query, would the cost of having a wrapper function around the original
>> function be too large? For example, if this function appears in a
>> WHERE clause against a table containing millions of rows.
>
> If your wrapper function is written in SQL and is trivial (eg ignore
> the third parameter and pass the other two on), the planner should be
> able to optimize right through it. Best way to find out is with
> EXPLAIN, which I've been using a good bit lately. The optimizer's
> pretty smart.
For example consider following rough example:
postgres=# create table nums as select * from generate_series(1,1000000) as num;
SELECT
Time: 1185.589 ms
postgres=# select count(*) from nums where num > 3450;
count
--------
996550
(1 row)
Time: 183.987 ms
postgres=# create or replace function gt(n int, m int) returns boolean as $$
begin
return n > m;
end;
$$
language plpgsql;
CREATE FUNCTION
Time: 1.080 ms
postgres=# select count(*) from nums where gt(num, 3450);
count
--------
996550
(1 row)
Time: 1327.800 ms
postgres=# create or replace function gt3(n int, m int, o int) returns
boolean as $$
begin
return gt(n, m);
end;
$$
language plpgsql;
CREATE FUNCTION
Time: 1.073 ms
postgres=# select count(*) from nums where gt3(num, 3450, 0);
count
--------
996550
(1 row)
Time: 2356.576 ms
postgres=# explain select count(*) from nums where gt3(num, 3450, 0);
QUERY PLAN
--------------------------------------------------------------------
Aggregate (cost=265258.34..265258.35 rows=1 width=0)
-> Seq Scan on nums (cost=0.00..264425.00 rows=333333 width=0)
Filter: gt3(num, 3450, 0)
(3 rows)
--
Amit Langote
On Thu, Jun 20, 2013 at 11:35 AM, Amit Langote <amitlangote09@gmail.com> wrote: > On Thu, Jun 20, 2013 at 10:27 AM, Chris Angelico <rosuav@gmail.com> wrote: >> If your wrapper function is written in SQL and is trivial (eg ignore >> the third parameter and pass the other two on), the planner should be >> able to optimize right through it. Best way to find out is with >> EXPLAIN, which I've been using a good bit lately. The optimizer's >> pretty smart. > > For example consider following rough example: > > postgres=# create table nums as select * from generate_series(1,1000000) as num; > SELECT > Time: 1185.589 ms > postgres=# select count(*) from nums where num > 3450; > count > -------- > 996550 > (1 row) > > Time: 183.987 ms > > postgres=# create or replace function gt(n int, m int) returns boolean as $$ > begin > return n > m; > end; > $$ > language plpgsql; > CREATE FUNCTION > Time: 1.080 ms > > postgres=# select count(*) from nums where gt(num, 3450); > count > -------- > 996550 > (1 row) > > Time: 1327.800 ms > Huge difference between 'language plpgsql' and 'language sql'. Here's my timings using your code - similar to your timings: rosuav=> select count(*) from nums where num > 3450; count -------- 996550 (1 row) Time: 293.836 ms rosuav=> select count(*) from nums where gt(num, 3450); count -------- 996550 (1 row) Time: 2412.186 ms rosuav=> select count(*) from nums where gt3(num, 3450, 0); count -------- 996550 (1 row) Time: 4332.554 ms Now here's the SQL version of the code: rosuav=> create or replace function gt(n int, m int) returns boolean as 'select n>m' language sql; CREATE FUNCTION Time: 39.196 ms rosuav=> select count(*) from nums where gt(num, 3450); count -------- 996550 (1 row) Time: 258.153 ms rosuav=> create or replace function gt3(n int, m int,o int) returns boolean as 'select gt(n,m)' language sql; CREATE FUNCTION Time: 21.891 ms rosuav=> select count(*) from nums where gt3(num, 3450, 0); count -------- 996550 (1 row) Time: 258.998 ms The original, the one with the SQL function, and the one with two SQL functions, all are within margin of error. (Repeated execution shows times varying down as far as 237ms for the last one.) ChrisA
On Thu, Jun 20, 2013 at 10:54 AM, Chris Angelico <rosuav@gmail.com> wrote: > On Thu, Jun 20, 2013 at 11:35 AM, Amit Langote <amitlangote09@gmail.com> wrote: >> On Thu, Jun 20, 2013 at 10:27 AM, Chris Angelico <rosuav@gmail.com> wrote: >>> If your wrapper function is written in SQL and is trivial (eg ignore >>> the third parameter and pass the other two on), the planner should be >>> able to optimize right through it. Best way to find out is with >>> EXPLAIN, which I've been using a good bit lately. The optimizer's >>> pretty smart. >> >> For example consider following rough example: >> >> postgres=# create table nums as select * from generate_series(1,1000000) as num; >> SELECT >> Time: 1185.589 ms >> postgres=# select count(*) from nums where num > 3450; >> count >> -------- >> 996550 >> (1 row) >> >> Time: 183.987 ms >> >> postgres=# create or replace function gt(n int, m int) returns boolean as $$ >> begin >> return n > m; >> end; >> $$ >> language plpgsql; >> CREATE FUNCTION >> Time: 1.080 ms >> >> postgres=# select count(*) from nums where gt(num, 3450); >> count >> -------- >> 996550 >> (1 row) >> >> Time: 1327.800 ms >> > > Huge difference between 'language plpgsql' and 'language sql'. Here's > my timings using your code - similar to your timings: > Umm, my bad! I almost forgot I could write pure SQL function bodies. Although, why does following happen? (sorry, a 8.4.2 installation) : postgres=# create or replace function gt(n int, m int) returns boolean as 'select n>m' language sql; ERROR: column "n" does not exist LINE 2: as 'select n>m' language sql; -- Amit Langote
On Thu, Jun 20, 2013 at 12:09 PM, Amit Langote <amitlangote09@gmail.com> wrote: > Umm, my bad! I almost forgot I could write pure SQL function bodies. > Although, why does following happen? (sorry, a 8.4.2 installation) : > > postgres=# create or replace function gt(n int, m int) returns boolean > as 'select n>m' language sql; > ERROR: column "n" does not exist > LINE 2: as 'select n>m' language sql; Hmm, no idea. I'm using 9.2.4, could well have been changes. ChrisA
On Thu, Jun 20, 2013 at 11:10 AM, Chris Angelico <rosuav@gmail.com> wrote: > On Thu, Jun 20, 2013 at 12:09 PM, Amit Langote <amitlangote09@gmail.com> wrote: >> Umm, my bad! I almost forgot I could write pure SQL function bodies. >> Although, why does following happen? (sorry, a 8.4.2 installation) : >> >> postgres=# create or replace function gt(n int, m int) returns boolean >> as 'select n>m' language sql; >> ERROR: column "n" does not exist >> LINE 2: as 'select n>m' language sql; > > Hmm, no idea. I'm using 9.2.4, could well have been changes. > Hmm, I guess in 8.4.2, one needs to refer to function arguments as $1, $2 ... postgres=# create or replace function gt(n int, m int) returns boolean as 'select $1>$2' language sql; CREATE FUNCTION postgres=# create or replace function gt3(n int, m int, o int) returns boolean as 'select gt($1,$2)' language sql; CREATE FUNCTION postgres=# select count(*) from nums where num > 3450; count -------- 996550 (1 row) Time: 126.184 ms postgres=# select count(*) from nums where gt(num, 3450); count -------- 996550 (1 row) Time: 130.754 ms postgres=# select count(*) from nums where gt3(num, 3450, 0); count -------- 996550 (1 row) Time: 140.031 ms And yes. OP can go ahead with his migration using this suggested wrapping function idea. -- Amit Langote
On Thu, Jun 20, 2013 at 12:34 PM, Amit Langote <amitlangote09@gmail.com> wrote: > On Thu, Jun 20, 2013 at 11:10 AM, Chris Angelico <rosuav@gmail.com> wrote: >> On Thu, Jun 20, 2013 at 12:09 PM, Amit Langote <amitlangote09@gmail.com> wrote: >>> Umm, my bad! I almost forgot I could write pure SQL function bodies. >>> Although, why does following happen? (sorry, a 8.4.2 installation) : >>> >>> postgres=# create or replace function gt(n int, m int) returns boolean >>> as 'select n>m' language sql; >>> ERROR: column "n" does not exist >>> LINE 2: as 'select n>m' language sql; >> >> Hmm, no idea. I'm using 9.2.4, could well have been changes. >> > > Hmm, I guess in 8.4.2, one needs to refer to function arguments as $1, $2 ... Ah, okay. I'm not all that familiar with different versions of PostgreSQL; I used it first back in the 1990s, then didn't use it for years (was all DB2), and now picked it up again at version 9.0/9.1, moving to 9.2 when it came out. > And yes. OP can go ahead with his migration using this suggested > wrapping function idea. Absolutely! ChrisA
>PostgreSQL has no such capability. Unless you need that and >want to code it yourself, the best solution would be to >write a function that just ignores the third argument. For time being I will write a function that just ignores the third argument. but if we really want to create such function like DB2 TO_CHAR() we need to code it. like setting locale as third argument or format string according to third argument and return it. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-tp5759820p5760265.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.