Thread: Function round(double precision, integer) does not exists trouble in 7.3.1, did work on 7.2.1

I am having trouble with a function round that used to work in 7.2.1

PostgreSQL was updated to 7.3.1 from 7.2.1, the data was re-loaded into
7.3.1 with no complaints from the DB engine but, at application run
time, we do get the error:

Function round(double precision, integer) does not exists

Code executed and that worked fine in 7.2.1 is:

selectclientes.id_cliente,clientes.codigo_cliente,clientes.total,clientes.deuda,clientes_pagos,total_pagos,
diferencia      from 
(
select clientes.id_cliente,clientes.codigo_cliente,clientes.total,clientes.deuda,(clientes.total-clientes.deuda) as
clientes_pagos,sum(sum)as total_pagos,       round((clientes.total-clientes.deuda)-sum(sum),4) as diferencia from    (
 select         codigo_cliente,         sum(total_pago)         from pagos        where estatus!='C'        group by
codigo_cliente 
    union    select         codigo_cliente,         sum(total_nota)        from notas_credito        where estatus!='C'
      group by codigo_cliente    )     as TT 
where codigo_cliente=clientes.codigo_clientegroup by
clientes.codigo_cliente,clientes.id_cliente,clientes.total,clientes.deuda,clientes_pagos
)
as TT1
where diferencia>0;


Question is, what changed in 7.3.1 in reference to the round function.

Tom Lane stated in a previous post (attached below) that "There isn't a
round(double, integer) function in 7.3 and there wasn't one in 7.2
either."    I am not so sure that is the case in our installation.

¿ How it can be explained then that it worked fine in a stock install
(rpm from RedHat Linux 7.3) of PostgreSQL 7.2.1 ?

Are we missing something here...?

Any comments or sugestions are welcome.

Felipe Barousse



> From: pgsql-bugs@postgresql.org
> To: pgsql-bugs@postgresql.org
> Subject: Bug #835: round(double precision,integer) function in 7.3 does not work
> Date: Wed, 4 Dec 2002 16:26:28 -0500 (EST)
>
>
> Eugene von Niederhausern (evonnied@niederworks.com) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> round(double precision,integer)  function in 7.3 does not work
>
> Long Description
> round(double precision,integer) does not work with 7.3. This function did work with 7.2.x.
>
> Sample Code
> select round((extract(epoch from CURRENT_TIMESTAMP) - extract(epoch from '2002-12-01'::timestamp))/60.0/60.0/24.0,2);
/**does not work with 7.3 **/ 
>
> select round((extract(epoch from CURRENT_TIMESTAMP) - extract(epoch from '2002-12-01'::timestamp))/60.0/60.0/24.0);
/**does work with 7.3 **/ 
>
> select round(((extract(epoch from CURRENT_TIMESTAMP) - extract(epoch from
'2002-12-01'::timestamp))/60.0/60.0/24.0)::numeric,2);/** does work with 7.3 **/ 
>
> No file was uploaded with this report
>
>
>
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: evonnied@niederworks.com, pgsql-bugs@postgresql.org
> Subject: Re: Bug #835: round(double precision,integer) function in 7.3 does not work
> Date: Wed, 04 Dec 2002 17:55:29 -0500
>
>
> > round(double precision,integer)  function in 7.3 does not work
>
> There isn't a round(double, integer) function in 7.3, and there wasn't
> one in 7.2 either.
>
> 7.3 forces you to write the cast to numeric explicitly.
>
> regards,

Hi Felipe,

On 04 Jan 2003 13:58:13 -0600, you wrote:

>I am having trouble with a function round that used to work in 7.2.1
>
>PostgreSQL was updated to 7.3.1 from 7.2.1, the data was re-loaded into
>7.3.1 with no complaints from the DB engine but, at application run
>time, we do get the error:
>
>Function round(double precision, integer) does not exists
>
>Code executed and that worked fine in 7.2.1 is:
>
>select
>    clientes.id_cliente,

[...]

have you tried to follow the Tom Lane suggestion to explicit cast the
first parameter in the call to round function ?

Anyway, to avoid writing queries and triggers to port existing
database to newer versions of Postgres you can define the following
very simple function:

-----------------------------------------------------------------

create function round (double precision, integer) returns double
precision as '

       select cast(round(cast($1 as numeric),$2) as double precision);

' LANGUAGE SQL with(iscachable) ;

----------------------------------------------------------------


I hope this can help someone.


Regards,
                        Silvio Scarpati