[SQL] argument type problem with plpgsql function

2005-11-22 Thread Luca Pireddu 
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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] argument type problem with plpgsql function

2005-11-22 Thread Stephan Szabo 
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?

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] argument type problem with plpgsql function

2005-11-22 Thread Luca Pireddu 
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

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] argument type problem with plpgsql function

2005-11-22 Thread Tom Lane 
Luca Pireddu [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] argument type problem with plpgsql function

2005-11-22 Thread Luca Pireddu 
On November 22, 2005 20:24, Tom Lane wrote:
 Luca Pireddu [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org