From: | Amit Langote <amitlangote09(at)gmail(dot)com> |
---|---|
To: | Chris Angelico <rosuav(at)gmail(dot)com> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Migration from DB2 to PostgreSQL |
Date: | 2013-06-20 02:34:06 |
Message-ID: | CA+HiwqHtwksRu1TCKT+bSRDKHRnrGqarCOfGFNn6exbfqfSs7Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Jun 20, 2013 at 11:10 AM, Chris Angelico <rosuav(at)gmail(dot)com> wrote:
> On Thu, Jun 20, 2013 at 12:09 PM, Amit Langote <amitlangote09(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Angelico | 2013-06-20 02:37:00 | Re: Migration from DB2 to PostgreSQL |
Previous Message | Chris Angelico | 2013-06-20 02:10:56 | Re: Migration from DB2 to PostgreSQL |