pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedargument type problem with plpgsql function
5+ messages / 3 participants
[nested] [flat]
* argument type problem with plpgsql function
@ 2005-11-23 00:08 Luca Pireddu <luca@cs.ualberta.ca>
2005-11-23 00:13 ` Re: argument type problem with plpgsql function Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 5+ messages in thread
From: Luca Pireddu @ 2005-11-23 00:08 UTC (permalink / raw)
To: pgsql-sql
I wrote a little function that has to work with big numbers
CREATE OR REPLACE FUNCTION blast_evalue(seq_len bigint, db_size bigint,
bit_score double precision)
RETURNS double precision AS $$
BEGIN
RETURN 2^(bit_score) * db_size * seq_len;
END;
$$
LANGUAGE 'plpgsql'
IMMUTABLE
RETURNS NULL ON NULL INPUT;
but it doesn't work properly unless I cast the db_size parameter when I call
the function:
select blast_evalue(273, 8903836, -55.4546);
blast_evalue
--------------
2430747228 <- wrong number. This is 273 * 8903836
(1 row)
select blast_evalue(273, 8903836::bigint, -55.4546);
blast_evalue
---------------------
4.9231356421437e-08 <- that's correct
(1 row)
I don't understand why the cast is necessary. Is there a way to make this
work without it?
Thanks
Luca
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: argument type problem with plpgsql function
2005-11-23 00:08 argument type problem with plpgsql function Luca Pireddu <luca@cs.ualberta.ca>
@ 2005-11-23 00:13 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-11-23 02:22 ` Re: argument type problem with plpgsql function Luca Pireddu <luca@cs.ualberta.ca>
0 siblings, 1 reply; 5+ messages in thread
From: Stephan Szabo @ 2005-11-23 00:13 UTC (permalink / raw)
To: Luca Pireddu <luca@cs.ualberta.ca>; +Cc: pgsql-sql
On Tue, 22 Nov 2005, Luca Pireddu wrote:
> I wrote a little function that has to work with big numbers
>
> CREATE OR REPLACE FUNCTION blast_evalue(seq_len bigint, db_size bigint,
> bit_score double precision)
> RETURNS double precision AS $$
> BEGIN
> RETURN 2^(bit_score) * db_size * seq_len;
> END;
> $$
> LANGUAGE 'plpgsql'
> IMMUTABLE
> RETURNS NULL ON NULL INPUT;
>
> but it doesn't work properly unless I cast the db_size parameter when I call
> the function:
>
> select blast_evalue(273, 8903836, -55.4546);
> blast_evalue
> --------------
> 2430747228 <- wrong number. This is 273 * 8903836
> (1 row)
>
> select blast_evalue(273, 8903836::bigint, -55.4546);
> blast_evalue
> ---------------------
> 4.9231356421437e-08 <- that's correct
> (1 row)
>
> I don't understand why the cast is necessary. Is there a way to make this
> work without it?
I got the same answer (the second) for both calls from my 8.0 and 8.1
setups, what version were you trying on?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: argument type problem with plpgsql function
2005-11-23 00:08 argument type problem with plpgsql function Luca Pireddu <luca@cs.ualberta.ca>
2005-11-23 00:13 ` Re: argument type problem with plpgsql function Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2005-11-23 02:22 ` Luca Pireddu <luca@cs.ualberta.ca>
2005-11-23 03:24 ` Re: argument type problem with plpgsql function Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Luca Pireddu @ 2005-11-23 02:22 UTC (permalink / raw)
To: pgsql-sql
On November 22, 2005 17:13, Stephan Szabo wrote:
> On Tue, 22 Nov 2005, Luca Pireddu wrote:
> [snip]
>
> I got the same answer (the second) for both calls from my 8.0 and 8.1
> setups, what version were you trying on?
>
I forgot to mention that. I'm using version 8.0.4, built from source.
I just tried it at home on a postgresql 8.0.3 server (debian package) and it
worked the way it's supposed to. Puzzling...
Luca
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: argument type problem with plpgsql function
2005-11-23 00:08 argument type problem with plpgsql function Luca Pireddu <luca@cs.ualberta.ca>
2005-11-23 00:13 ` Re: argument type problem with plpgsql function Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-11-23 02:22 ` Re: argument type problem with plpgsql function Luca Pireddu <luca@cs.ualberta.ca>
@ 2005-11-23 03:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-23 05:41 ` Re: argument type problem with plpgsql function Luca Pireddu <luca@cs.ualberta.ca>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2005-11-23 03:24 UTC (permalink / raw)
To: Luca Pireddu <luca@cs.ualberta.ca>; +Cc: pgsql-sql
Luca Pireddu <luca@cs.ualberta.ca> writes:
> I just tried it at home on a postgresql 8.0.3 server (debian package) and it
> worked the way it's supposed to. Puzzling...
Maybe you have more than one blast_evalue() function with different
argument types?
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: argument type problem with plpgsql function
2005-11-23 00:08 argument type problem with plpgsql function Luca Pireddu <luca@cs.ualberta.ca>
2005-11-23 00:13 ` Re: argument type problem with plpgsql function Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-11-23 02:22 ` Re: argument type problem with plpgsql function Luca Pireddu <luca@cs.ualberta.ca>
2005-11-23 03:24 ` Re: argument type problem with plpgsql function Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-11-23 05:41 ` Luca Pireddu <luca@cs.ualberta.ca>
0 siblings, 0 replies; 5+ messages in thread
From: Luca Pireddu @ 2005-11-23 05:41 UTC (permalink / raw)
To: pgsql-sql
On November 22, 2005 20:24, Tom Lane wrote:
> Luca Pireddu <luca@cs.ualberta.ca> writes:
> > I just tried it at home on a postgresql 8.0.3 server (debian package) and
> > it worked the way it's supposed to. Puzzling...
>
> Maybe you have more than one blast_evalue() function with different
> argument types?
>
You were right! A blast_evalue(integer, integer, double precision). Thank
you for your help.
Luca
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-11-23 05:41 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-23 00:08 argument type problem with plpgsql function Luca Pireddu <luca@cs.ualberta.ca>
2005-11-23 00:13 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2005-11-23 02:22 ` Luca Pireddu <luca@cs.ualberta.ca>
2005-11-23 03:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-11-23 05:41 ` Luca Pireddu <luca@cs.ualberta.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox