PostgreSQL FUNCTION return problem

From: "Rafael Barrera Oro" <borafael(at)gmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: PostgreSQL FUNCTION return problem
Date: 2008-04-07 14:35:22
Message-ID: a964044f0804070735j16379088w289ed1a4317da95b@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hello, i have the following problem and i was wondering if you coulde help
me. I'll try to describe it as in depth as i can.

I have a FUNCTION in a postgresql database which subtracts two DOUBLE
PRECISION FIELDS and returns the result. The problem is that when the result
should be zero, the number i
get is a number ridiculously close to zero in scientific notation (for
example 2.4697823124E-14) but not zero. I know (or at least i think i know)
the problem lies within the way postgresql and Java communicate because if a
connect through a console to the db and run the function i get the result
right (zero) but if i debug the java code i get ther wrong
non-zero-but-very-close result

this is the function:

CREATE OR REPLACE FUNCTION getSaldoParticipante(BIGINT) RETURNS DOUBLE
PRECISION AS '
DECLARE
idParticipante ALIAS FOR $1;
result DOUBLE PRECISION;
BEGIN

result := 0;

Select (Select CASE WHEN saldo ISNULL then 0 else saldo end FROM (Select
sum(PG.saldo) as saldo FROM pagos.pagos PG WHERE PG.saldo > 0 AND
PG.participante=idParticipante AND PG.deleted = false) tmp)-(Select CASE
WHEN saldo ISNULL then 0 else saldo end FROM (Select sum(DE.saldo) as saldo
FROM pagos.deudas DE WHERE DE.deleted = false AND
DE.participante=idParticipante) tmp) INTO result;

RETURN result;

END;'
LANGUAGE 'plpgsql' CALLED ON NULL INPUT;

this is the Java code where i get the result that should be zero but instead
is just very close to zero

String query = "select getSaldoParticipante(?)";
params.add(new ParameterValue(idParticipante, Types.BIGINT));

try {
conn = tx.getConnection();

//preparo y ejecuto el statement
pstmt = prepareStatement (conn, query, params);
rs = pstmt.executeQuery();

//itero los registros
while (rs.next()) {
//the problems appears at the following line
result = rs.getDouble("getSaldoParticipante");
}
}
catch(Exception e){
...

the only thing that works here is to use rs.getInt instead of rs.getDouble,
but that is not good enough since i dont want to truncate the decimal part
of the number.

So, a lot of thanks in advance, any help will be greatly appreciated.

¡Saludos!

Rafael

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Craig Ringer 2008-04-07 14:50:54 Re: PostgreSQL FUNCTION return problem
Previous Message Mats Ekelund 2008-04-07 11:19:18 Re: Issue with DataBaseMetaData.GetTypeInfo()