Обсуждение: Representation of big integer numbers.

Hi,

 Is there a way to display big integer numbers splitted by periods in
PostgreSQL queries?

Example: 123.456.789 instead of 123456789 .

            Thanks in advance

--------------------------------------------------------------------------------
Sebestyén Zoltán <szoli@netvisor.hu>    I'm believing that the Holy Spirit is
                    gonna allow the hand, and the foot, and
MAKE INSTALL NOT WAR            the mouth, just to begin to speak, and
                                        to minister, and to heal coordinated by
                    the head.

I use UNIX because reboots are for hardware upgrades.

            -- Eagerly waiting for FreeBSD 2.2.8 --

Memphisto wrote:
>
> Hi,
>
>  Is there a way to display big integer numbers splitted by periods in
> PostgreSQL queries?
>
> Example: 123.456.789 instead of 123456789 .
>
>                         Thanks in advance

You can create a function to format numbers.

See attached example.

-Jose'--- la funzione dec(float) ritorna la parte decimale come un intero
-- la limitazione e' che ha solo 3 cifre arrotondate 000-999
-- purtroppo la differenza tra un float e dtrunc(float) non funziona a
-- dovere, quindi ho usato la funzione date_parte('millisecond',float)
-- che tratta la parte decimale di un float come milisecondi.

drop function dec(float);
create function dec(float) returns text as
'
declare
    txt text;
begin
    --get decimal part...
    txt:= dround(datetime_part(''millisecond'',$1));
    if textlen(txt) = 2 then
        txt:= ''0'' || txt;
    end if;
    if textlen(txt) = 1 then
        txt:= ''00'' || txt;
    end if;
    return txt;
end;
' language 'plpgsql';

-- funzioni per la formattazione di interi e float

drop function format(float4,text);
create function format(float4,text) returns text as
'
begin
    return format(float8($1),$2);
end;
' language 'plpgsql';
drop function format(float8,text);
create function format(float8,text) returns text as
'declare
    fbak text;
    vbak int8;
    out text;
    fout text;
    lh text;
    res1 int8;
    res float8;
    i int2;
    df int2;
    sval text;
begin
        vbak:= dtrunc($1);
        fbak:= $2;
    res:=$1;
     if $1 < 0 then
        res := -(res);
    else
        res := res;
    end if;

        df:= textlen(fbak);
        i:= textpos(fbak,'','');
        if i > 0 then
         fbak:= substr(fbak,1,i - 1);
    end if;
    lh:=dec(res);
    fout:= format(vbak,fbak);
    if i = 0 then
        return fout;
    end if;
    out:= fout || ('','');
    out:= out || (substr(lh || ''0000000000000000'',1,df - i));
    return out;
end;
' language 'plpgsql';


drop function format(int8,text);
create function format(int8,text) returns text as
'declare
    fbak text;
    vbak int8;
    out text;
    sign int2;
    num char(1);
    car char(1);
    car1 char(1);
    car0 char(1);
    lf int2;
    bf int2;
    lv int2;
    sval text;
begin
        vbak := $1;
        fbak := $2;


    if vbak < 0 then
        sign := 1;
        vbak := -(vbak);
    else
        sign := 0;
    end if;

    lf := textlen(fbak);
    bf := lf;
    sval := vbak;
    lv := textlen(sval);

    if lv > lf then
        raise exception ''the value % is greater than %'',$1,$2;
    end if;

    while (lv>0 or lf>0) loop
        car:= substr(fbak,lf,1);
        car0:= substr(fbak,lf+1,1);
        car1:= substr(fbak,lf - 1,1);

        if lv > 0 then
            if lf=0 then
                raise exception ''The value % is greater than %'',$1,$2;
            end if;
            if car = ''#'' or car = ''&'' 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 = 1 then
                sign :=  2;
                fbak := substr(fbak,1,lf - 1) || (''-'' || substr(fbak,lf + 1));
            else
                if sign = 2 or car <> ''&'' then
                    if car0 = ''-'' or car0 = '' '' then
                        fbak := substr(fbak,1,lf - 1) || ('' '' || substr(fbak,lf + 1));
                    else
                        if car1 = ''#'' and (car <> ''#'' and car <> ''&'') then
                            fbak := substr(fbak,1,lf - 1) || ('' '' || substr(fbak,lf + 1));
                        else
                            if car1 = ''#'' then
                                fbak := substr(fbak,1,lf - 1) || ('' '' || substr(fbak,lf + 1));
                            end if;
                        end if;
                    end if;
                else
                    if car = ''&'' then
                        if car0 <> '' '' then
                            fbak := substr(fbak,1,lf - 1) || (''0'' || substr(fbak,lf + 1));
                        else
                            fbak := substr(fbak,1,lf - 1) || ('' '' || substr(fbak,lf + 1));
                        end if;
                    end if;
                end if;
            end if;
            lf := lf - 1;
        end if;
    end loop;

    return fbak;
end;
' language 'plpgsql';

drop function format(int4,text);
create function format(int4,text) returns text as
'
begin
    return format(int8($1),$2);
end;
' language 'plpgsql';

drop function format(int2,text);
create function format(int2,text) returns text as
'
begin
    return format(int4($1),$2);
end;
' language 'plpgsql';

select format(3112198,'(&&&)##-###'),
       format(311298,'##/##/19##'),
       format(3112198,'#''###''###'),
       format(3112198,'<&&&_&&&_&&&>'),
       format(311,'&''&&&''&&&'),
       format(56789.1234,'&.&&&.###,####'),
       format(3.454,'###,###');
select format(3.004,'###,###');