Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.1/8.9.1) with ESMTP id JAA15980 for ; Mon, 14 Dec 1998 09:38:54 -0500 (EST) (envelope-from sferac@bo.nettuno.it) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.6/NETTuno 3.1) with ESMTP id PAA02776; Mon, 14 Dec 1998 15:38:39 +0100 (MET) Received: from blu.sferacarta.com (mail.sferacarta.com) [10.20.30.12] by proxy.sferacarta.com with smtp (Exim 2.05 #1 (Debian)) id 0zpaTg-0002HS-00; Mon, 14 Dec 1998 16:04:08 +0000 Date: Mon, 14 Dec 1998 15:11:56 +0100 From: Sferacarta Software X-Mailer: The Bat! (v1.017) UNREG Reply-To: Sferacarta Software Organization: Sferacarta Software Priority: Normal Message-ID: <8633.981214@bo.nettuno.it> To: Jerome Knobl , pgsql-sql@postgresql.org Subject: Re[2]: [SQL] Howto format a float8 number? References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit HM> At 9:59 +0200 on 7/12/98, Jerome Knobl wrote: >> I want to print a floating number like this : >> 10.1 -> 10.10 >> or : 10000.1 -> 10'000.10 >> or : 0 -> 0.00 >> etc... You can write your own function to format data. I wrote the following function to format int4 using pl/pgSQL: create function format(int4,text) returns text as 'declare fbak text; vbak int4; out text; sign boolean; num char(1); i int2; lf int2; bf int2; lv int2; sval text; begin vbak := $1; fbak := $2; if lv > lf then raise exception ''the value % is greater than %'',$1,$2; end if; if vbak < 0 then sign := ''t''; vbak := -(vbak); else sign := ''f''; end if; lf := length(fbak); bf := lf; sval := text(vbak); lv := length(sval); while (lv>0 or lf>0) loop if lv > 0 then if lf=0 then raise exception ''the value % is greater than %' end if; if substr(fbak,lf,1)=''#'' then num := substr(sval,lv,1); out := substr(fbak,1,lf - 1) || num; if bf > lf then fbak := out || substr(fbak,lf+1); else fbak := out; end if; lf := lf - 1; lv := lv - 1; else lf := lf - 1; end if; else if sign then sign := ''false''; fbak := substr(fbak,1,lf - 1) || (''-'' || subst else fbak := substr(fbak,1,lf - 1) || ('' '' || subst end if; lf := lf - 1; end if; end loop; return fbak; end; ' language 'plpgsql'; EXAMPLES: select format(13954323,'#(##)##-###'); format ----------- 1(39)54-323 (1 row) select format(31121998,'## ## ####'); format ---------- 31 12 1998 (1 row) select format(311298,'##/##/19##'); format ---------- 31/12/1998 (1 row) select format(1311000,'#,###,###'); format --------- 1,311,000 (1 row) -Jose'-