Skip site navigation (1) Skip section navigation (2)

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

From: Ennio-Sr <nasr(dot)laili(at)tin(dot)it>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: How to transform pg_7.4 function 'to_number' to 8.4 equivalent?
Date: 2008-10-18 16:54:10
Message-ID: 20081018165410.GA4467@deby.ei.hnet (view raw or flat)
Thread:
Lists: pgsql-novice
* 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 <-- !!!


In response to

pgsql-novice by date

Next:From: Ridvan Lakas ng Bayan S. BaluyosDate: 2008-10-20 03:12:12
Subject: ERROR: could not find tuple for attrdef NNNNNNNN
Previous:From: Tom LaneDate: 2008-10-17 18:43:44
Subject: Re: How to transform pg_7.4 function 'to_number' to 8.4 equivalent?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group