Search Postgresql Archives

Re: Migration from DB2 to PostgreSQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Jun 20, 2013 at 11:10 AM, Chris Angelico <rosuav@xxxxxxxxx> wrote:
> On Thu, Jun 20, 2013 at 12:09 PM, Amit Langote <amitlangote09@xxxxxxxxx> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux