PostgreSQL总位数受限numeric类型实现优化及自定义类型可行性咨询

一、现有实现的已知Bug #

• 整数位溢出问题：当输入值的整数部分位数已经超过指定的总位数d时，返回结果会超出长度限制。比如执行num_flex(1000000, 6)（100万为7位整数），现有代码返回1000000，总位数7，不符合d=6的要求。
• 性能优化空间缺失：现有函数没有标记为immutable不可变函数，无法被查询优化器用于常量折叠、索引表达式等场景，执行效率偏低。
• 边界逻辑冗余：v=1、v=-1的场景被归类到else分支，实际处理结果和绝对值小于1的场景一致，逻辑可简化但不影响运行结果。

二、更优实现方案 #

你需要的能力本质是保留指定数量的有效数字，PostgreSQL 12及以上版本原生支持带有效数字参数的round函数，不需要自行编写分支判断，实现更简洁且覆盖所有边界场景：

create function num_flex(v numeric, d int) returns numeric as 
$$
select trim_scale(round(v, d, 'sig'));
$$
language sql immutable;

如果你的需求是禁止科学计数法、要求普通数字格式下总显示长度不超过d，可额外添加校验逻辑，当整数部分长度超过d时抛出异常即可。

三、自定义类型需求可实现 #

不需要修改PostgreSQL内核，用系统自带的域（DOMAIN）和类型转换能力即可实现，以num_flex6为例：

1. 首先创建自定义域

create domain num_flex6 as numeric;

2. 创建对应的转换函数

create function to_num_flex6(numeric) returns num_flex6 as $$
select trim_scale(round($1, 6, 'sig'))::num_flex6;
$$ language sql immutable;

3. 注册赋值转换规则

create cast (numeric as num_flex6)
with function to_num_flex6(numeric)
as assignment;

配置完成后即可直接调用：

select 12.123456789::num_flex6; -- 返回12.1235
select 12.123456789::num_flex7; -- 返回12.12346

按照上述流程重复创建num_flex2到num_flex9对应的域和转换规则即可，全程不需要编写C扩展，适配PostgreSQL 13及以上版本。

内容的提问来源于stack exchange，提问作者ftimmer