postgresql函数-根据文件大小自动转换为合适的单位

PostgreSQL自带转换的系统管理函数,但是只精确到整数,且最大单位只到TB,因此使用了自定义函数:

pg_size_pretty(bigint) 
pg_size_pretty(numeric)  

1.函数定义

drop function if exists appropriate_units(bigint);
create or replace function appropriate_units(bigint)
    returns text
as $$
	with cte as(
		select ARRAY['B', 'KB', 'MB', 'GB', 'TB','PB','EB','ZB','YB','BB'] as units,trunc(log($1)/log(1024))::integer as curindex
	),precision as(
		select units,curindex,
			(case when curindex<2 then
				0
			when curindex=2 then
				1
			else
				2
			end) as prec
		from cte
	) select 
			(case when 0=$1 then
				'0byte'
			else
				( round(($1 / pow(1024,curindex))::numeric,prec)::text || units[curindex + 1] )
			end) as size
	from precision;
$$ language sql;

注意事项

1. 函数支持的最大单位为TB,超过TB范围将返回null值 ,如果要支持更多的单位,请在cte中的ARRAY数组添加.
2. 计算机存储单位为B，KB、MB、GB、TB、PB、EB、ZB、YB、BB
3. 转换后的值byte,KB精确到整数,MB精确到小数后一位,其它精确到小数后两位

2.使用方法

select appropriate_units(327),appropriate_units(32768),appropriate_units(3276800),appropriate_units(3276800000000),appropriate_units(327680000000000);

appropriate_units查询结果

与pg_size_pretty对比

select pg_size_pretty(327::bigint),pg_size_pretty(32768::bigint),pg_size_pretty(3276800::bigint),pg_size_pretty(3276800000000::bigint),pg_size_pretty(327680000000000000::bigint);

pg_size_pretty查询结果