Re[2]: [SQL] Howto format a float8 number?

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'-