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