postgresql基于plpython实现数字转RMB中文

安装扩展

• 首先要确保数据库安装了plpython2u扩展，使用 \dx 命令查看
• 未安装的话使用 create extension plpython2u；命令安装

创建函数

create or replace function digit_to_rmb(digital numeric)returns text as $$
global digital
str_digital = str(digital)
chinese = {'1': '壹', '2': '贰', '3': '叁', '4': '肆', '5': '伍',
           '6': '陆', '7': '柒', '8': '捌', '9': '玖', '0': '零'}
chinese2 = ['拾', '佰', '仟', '万', '厘', '分', '角']
jiao = ''
bs = str_digital.split('.')
yuan = bs[0]
if len(bs) > 1:
    jiao = bs[1]
r_yuan = [i for i in reversed(yuan)]
count = 0
for i in range(len(yuan)):
    if i == 0:
        r_yuan[i] += '圆'
        continue
    r_yuan[i] += chinese2[count]
    count += 1
    if count == 4:
        count = 0
        chinese2[3] = '亿'

s_jiao = [i for i in jiao][:3]

j_count = -1
for i in range(len(s_jiao)):
    s_jiao[i] += chinese2[j_count]
    j_count -= 1
last = [i for i in reversed(r_yuan)] + s_jiao
last_str = ''.join(last)

for i in range(len(last_str)):
    digital = last_str[i]
    if digital in chinese:
        last_str = last_str.replace(digital, chinese[digital])

return last_str

$$ language plpython2u

可能存在的问题

issue.png

解决方法

• 查看plpython2u是否激活
  SELECT lanpltrusted FROM pg_language WHERE lanname LIKE 'plpython2u';
  没有的情况下结果是 f

• 激活
  UPDATE pg_language SET lanpltrusted = true WHERE lanname LIKE 'plpython2u';