argument type problem with plpgsql function

From: Luca Pireddu <luca(at)cs(dot)ualberta(dot)ca>
To: pgsql-sql(at)postgresql(dot)org
Subject: argument type problem with plpgsql function
Date: 2005-11-23 00:08:09
Message-ID: 200511221708.10563.luca@cs.ualberta.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2005-11-23 00:13:28 Re: argument type problem with plpgsql function
Previous Message Kyle Bateman 2005-11-22 20:24:59 Re: unplanned sub-select error?