PostgreSQL 13：如何实现自定义text转numeric转换（支持括号负数）

因为你没有超级用户权限，无法修改内置类型转换或创建自定义标量类型，也没法为域创建专属转换，下面是几个无需超级权限的实用方案：

方案1：自定义转换函数（灵活通用） #

创建一个普通函数处理文本到numeric的转换逻辑，显式调用即可，兼容正常数值文本和带括号的负数格式。

CREATE OR REPLACE FUNCTION txt_to_numeric(txt text) RETURNS numeric AS $$
BEGIN
  -- 匹配带括号的正数格式，提取内部数值转为负数
  IF txt ~ '^\((\d+(\.\d+)?)\)$' THEN
    RETURN -substring(txt from '^\((\d+(\.\d+)?)\)$')::numeric;
  ELSE
    -- 正常转换，保留PostgreSQL原生的数值转换错误处理
    RETURN txt::numeric;
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

使用示例： #

-- 带括号的文本转负数
SELECT txt_to_numeric('(15)');  -- 返回 -15
SELECT txt_to_numeric('(123.45)');  -- 返回 -123.45

-- 正常数值文本保持原样
SELECT txt_to_numeric('42');  -- 返回 42
SELECT txt_to_numeric('98.76');  -- 返回 98.76

-- 非法文本抛出原生错误（和直接::numeric一致）
SELECT txt_to_numeric('abc');  -- 报错：invalid input syntax for type numeric: "abc"

方案2：表触发器（自动处理特定字段） #

如果你的需求集中在某张表的字段转换上，可以用触发器在插入/更新时自动处理文本到数值的转换，无需手动调用函数。

示例步骤： #

1. 假设你有存储文本格式金额的表：

CREATE TABLE ledger (
  id serial PRIMARY KEY,
  amount_text text,  -- 存储原始文本，比如'(15)'或'20'
  amount numeric     -- 存储转换后的数值
);

2. 创建触发器处理函数：

CREATE OR REPLACE FUNCTION ledger_amount_convert() RETURNS trigger AS $$
BEGIN
  IF NEW.amount_text ~ '^\((\d+(\.\d+)?)\)$' THEN
    NEW.amount := -substring(NEW.amount_text from '^\((\d+(\.\d+)?)\)$')::numeric;
  ELSE
    NEW.amount := NEW.amount_text::numeric;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 绑定触发器到表：

CREATE TRIGGER ledger_before_insert_update
BEFORE INSERT OR UPDATE ON ledger
FOR EACH ROW EXECUTE FUNCTION ledger_amount_convert();

使用示例： #

INSERT INTO ledger (amount_text) VALUES ('(15)'), ('30'), ('(99.99)');

SELECT * FROM ledger;
-- 结果中amount字段会分别是 -15, 30, -99.99

方案3：视图封装（查询场景专用） #

如果主要是查询时需要转换，可创建视图封装转换逻辑，让用户直接查询视图获取转换后的值，无需关注转换细节。

CREATE VIEW ledger_view AS
SELECT id,
       amount_text,
       CASE
         WHEN amount_text ~ '^\((\d+(\.\d+)?)\)$' THEN -substring(amount_text from '^\((\d+(\.\d+)?)\)$')::numeric
         ELSE amount_text::numeric
       END AS converted_amount
FROM ledger;

使用示例： #

SELECT converted_amount FROM ledger_view WHERE amount_text = '(15)';
-- 返回 -15

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