Re: How to transform pg_7.4 function 'to_number' to 8.4 equivalent?

* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [171008, 14:43]:
> Ennio-Sr <nasr(dot)laili(at)tin(dot)it> writes:
> > after upgrading from pg_7.4 to pg_8.3 when I run this command:
>
> > psql finanza -c "SELECT titolo, var, pr_chius, pr_aper, \
> > diff_aper_ch*(-1) as \"diff. ass.\", \
> > to_number(diff_aper_ch*(-1)/pr_aper*100::float(8),'9999.000000') as \"sc. %\" \
>
> to_number works on text arguments. I suppose you could make this work
> like 7.4 by explicitly casting the expression to text, but the whole
> thing seems like the wrong approach altogether. What are you trying to
> accomplish --- round the result to six decimal places? If so, it'd be
> better to just cast it to numeric(20,6) or some such.
>
[Sorry! I sent this to you privately yesterday]
-----
Hmm...I did that at least 3 years ago and do not have any comments in my
script as to why I wrote it that way. The aim was to print the result of
that division with a few decimal points: probably the 'to_number' was a
requirement of vers. 7.4 in order to display the result according to the
template.
Now I've made a few 'blind' attempts to change it but got different type
of errors! It will take me many efforts to resume my fundamental
knowledge of docs ;-) ... unless you could point me to some particular
chapter ...
Thanks for your time.
Regards, Ennio
-----
In the meantime I managed to come out of the empasse: writing the
original query in the following way seems to work; however, I'm
sure that newer also means simpler, so there must be a simpler way to
write this query (i.e. a viable substitution for the old 'to_number')!

SELECT \
titolo, var, pr_chius, pr_aper, \
diff_aper_ch*(-1) as \"diff. ass.\", \
to_char(diff_aper_ch*(-1)/pr_aper*100::float(8),'9999.000000') as \"sc. %\" \
FROM \
ult_qq \
GROUP BY \
titolo, var, pr_chius, pr_aper, diff_aper_ch*(-1)
HAVING \
pr_chius>pr_aper and pr_aper>0 \
ORDER BY \
by titolo;

Regards,
Ennio
---------------
PS: Considering that my 'whole architecture' was working so smoothly,
would it be possible to go back to version 7.4?

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?//
Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°)
Ricevo solo messaggi Content-Type: plain/text (no html o multipart). )=(
!!! --> e-mail a mio nome via OE (M$) sono false e infette <-- !!!