Re: [GENERAL] Representation of big integer numbers.

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,'###,###');