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