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