发帖
雷达卡
环境
系统平台：Microsoft Windows (64-bit) 10
版本：5.6.4
文档用途
在Oracle数据库中，当对两个文本类型的数据执行减法操作时，系统会自动将其转换为数值类型进行计算。
然而，在HGDB中，默认情况下并不会将文本类型隐式转换为数值类型来进行减法运算。
为了实现与Oracle行为的兼容性，可以采用以下两种方式：
- 建立从text类型到numeric类型的隐式转换机制。
- 定义支持text与text之间进行减法操作的操作符。
详细信息
通过查看类型转换列表可以发现，当前系统中并未设置text类型向numeric类型的隐式转换。
highgo=# \dC
类型转换列表
来源类型 | 目标类型 | 函数 | 隐含的?
-----------------------------+-----------------------------+--------------------+----------
"char" | character | bpchar | 在指派中
"char" | character varying | text | 在指派中
"char" | integer | int4 | 否
"char" | text | text | 是
character | "char" | char | 在指派中
character | character | bpchar | 是
character | character varying | text | 是
character | name | name | 是
character | nvarchar2 | (binary coercible) | 是
character | text | text | 是
character | varchar2 | (binary coercible) | 是
character | xml | xml | 否
character varying | "char" | char | 在指派中
character varying | character | (binary coercible) | 是
character varying | character varying | varchar | 是
character varying | name | name | 是
character varying | nvarchar2 | (binary coercible) | 是
character varying | regclass | regclass | 是
character varying | text | (binary coercible) | 是
从上述输出可以看出，text类型无法自动转为numeric类型，因此直接对text字段做减法将不被支持，除非手动干预或添加相应的转换规则。
| 源数据类型 | 目标数据类型 | 转换方式 | 是否支持隐式转换 |
|---|---|---|---|
| nvarchar2 | character | (binary coercible) | 是 |
| nvarchar2 | character varying | (binary coercible) | 是 |
| nvarchar2 | date | (binary coercible) | 是 |
| nvarchar2 | double precision | (binary coercible) | 是 |
| nvarchar2 | integer | (binary coercible) | 是 |
| nvarchar2 | interval | (binary coercible) | 是 |
| nvarchar2 | numeric | (binary coercible) | 是 |
| nvarchar2 | nvarchar2 | nvarchar2 | 是 |
| nvarchar2 | real | (binary coercible) | 是 |
| nvarchar2 | smallint | (binary coercible) | 是 |
| nvarchar2 | text | (binary coercible) | 是 |
| nvarchar2 | timestamp without time zone | (binary coercible) | 是 |
| text | "char" | char | 在指派中 |
| text | character | (binary coercible) | 是 |
| text | character varying | (binary coercible) | 是 |
| text | name | name | 是 |
| text | nvarchar2 | (binary coercible) | 是 |
| text | regclass | regclass | 是 |
| text | varchar2 | (binary coercible) | 是 |
| text | xml | xml | 否 |
| character varying | varchar2 | (binary coercible) | 是 |
| character varying | xml | xml | 否 |
| varchar2 | bigint | (binary coercible) | 是 |
varchar2 | character | (binary coercible) | 是
varchar2 | character varying | (binary coercible) | 是
varchar2 | date | (binary coercible) | 是
varchar2 | double precision | (binary coercible) | 是
varchar2 | integer | (binary coercible) | 是
varchar2 | interval | (binary coercible) | 是
varchar2 | numeric | (binary coercible) | 是
varchar2 | real | (binary coercible) | 是
varchar2 | smallint | (binary coercible) | 是
varchar2 | text | (binary coercible) | 是
varchar2 | timestamp without time zone | (binary coercible) | 是
varchar2 | varchar2 | varchar2 | 是
(269 行记录)
尝试执行 text 类型之间的减法操作时会报错：
highgo=# select '10'::text - '2'::text;
ERROR: 42883: operator does not exist: text - text
第1行select '10'::text - '2'::text;
^
提示信息：No operator matches the given name and argument type(s). You might need to add explicit type casts.
[此处为图片1]
为解决该问题，可采用以下两种方式实现 text 类型数值的运算能力。
一、通过定义隐式类型转换实现
1.1 隐式转换语法说明
使用 CREATE CAST 命令可以创建自定义的数据类型转换规则。其语法结构如下：
CREATE CAST (源数据类型 AS 目标数据类型)
WITH FUNCTION 函数名称 [ (参数类型 [, ...]) ]
[ AS ASSIGNMENT | AS IMPLICIT ]
CREATE CAST (源数据类型 AS 目标数据类型)
WITHOUT FUNCTION
[ AS ASSIGNMENT | AS IMPLICIT ]
CREATE CAST (源数据类型 AS 目标数据类型)
WITH INOUT
[ AS ASSIGNMENT | AS IMPLICIT ]
其中：
- WITH INOUT 表示使用类型的 I/O 函数进行转换；
- AS IMPLICIT 表示该转换可在表达式中自动触发；
- AS ASSIGNMENT 表示仅在赋值场景下自动转换。
1.2 创建从 text 到 numeric 的隐式转换
执行以下命令注册一个隐式转换规则：
highgo=# create cast (text as numeric) with inout AS IMPLICIT;
CREATE CAST
此操作允许系统在需要 numeric 的上下文中自动将 text 类型值转换为 numeric。
1.3 验证转换效果
完成上述设置后，即可直接对 text 类型的数字字符串进行算术运算：
highgo=# select '10'::text - '2'::text;
?column?
----------
8
(1 行记录)
highgo=# select '10'::text - '2.8'::text;
?column?
----------
7.2
(1 行记录)
可见，text 类型已能参与数学运算，得益于隐式转换机制将其转为 numeric 处理。
[此处为图片2]
二、通过创建自定义操作符函数实现
2.1 定义用于处理 text 减法的函数
另一种方法是显式创建一个支持两个 text 参数并返回 numeric 结果的函数：
highgo=# create or replace function text_text(text, text) returns numeric as $$
highgo$# select $1::numeric - $2::numeric;
$$ language sql;
该函数将两个文本参数分别转换为 numeric 类型后执行减法操作，并返回结果。
后续可通过绑定此函数到特定操作符（如 "-"）来扩展数据库对 text-text 运算的支持。但需注意，默认情况下 PostgreSQL 不提供此类操作符，需手动注册操作符与函数映射关系才能实现无缝调用。
当前方案中，若仅需临时计算，可直接调用函数或依赖隐式转换方式更为简便高效。
2.2、运算函数测试
执行以下语句对自定义函数进行测试：
highgo=# select text_text('10.1', '20.222');
text_text
-----------
-10.122
(1 行记录)
2.3、创建基于函数的操作符
通过已定义的函数创建支持文本类型相减的操作符：
highgo=# create operator - (procedure=text_text, leftarg=text, rightarg=text);
CREATE OPERATOR
2.4、验证操作符功能并移除隐式转换
现已实现 text 与 text 类型之间的减法操作。为准确验证操作符行为，需先删除先前设置的隐式类型转换：
highgo=# drop cast (text as numeric);
DROP CAST
随后再次调用函数进行结果验证：
highgo=# select text_text('10.1', '20.222');
text_text
-----------
-10.122
(1 行记录)
继续测试其他数值组合：
highgo=# select text_text('10', '1.2');
text_text
-----------
8.8
(1 行记录)
京公网安备 11010802022788号
