Обсуждение: Function round(double precision, integer) does not exists trouble in 7.3.1, did work on 7.2.1
Function round(double precision, integer) does not exists trouble in 7.3.1, did work on 7.2.1
От
Felipe Barousse Boué
Дата:
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,
Re: Function round(double precision, integer) - Fast backward compatibility solution
От
Silvio Scarpati
Дата:
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