Postgres Pro
Downloads
Home   >   mailing lists

Thread: argument type problem with plpgsql function

argument type problem with plpgsql function

From
Luca Pireddu
Date:
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

Re: argument type problem with plpgsql function

From
Stephan Szabo
Date:
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?

Re: argument type problem with plpgsql function

From
Luca Pireddu
Date:
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

Re: argument type problem with plpgsql function

From
Tom Lane
Date:
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

Re: argument type problem with plpgsql function

From
Luca Pireddu
Date:
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