标签
PostgreSQL , Oracle , 兼容性 , text减text操作符
背景
Oracle的两个文本详见，会自动转换为数值进行相减操作。
PostgreSQL默认并不会将文本转换为数值进行相减操作。
为了实现兼容，有两种方法：
1、创建text到numeric的隐式转换
2、创建text-text的操作符。
PostgreSQL 内置CAST
可以看到varchar是有隐式转numeric的。但是text类型没有隐式转numeric。
postgres=# \dC
List of casts
Source type | Target type | Function | Implicit?
-----------------------------+-----------------------------+---------------------------+---------------
...........
character varying | "char" | char | in assignment
character varying | character | (binary coercible) | yes
character varying | character varying | varchar | yes
character varying | name | name | yes
character varying | numeric | (binary coercible) | yes
character varying | regclass | regclass | yes
character varying | text | (binary coercible) | yes
character varying | xml | xml | no
..........
text | "char" | char | in assignment
text | character | (binary coercible) | yes
text | character varying | (binary coercible) | yes
text | name | name | yes
text | regclass | regclass | yes
text | xml | xml | no
.................
(241 rows)
因此如果你使用的是varchar-varchar，可以自动算出结果来。
postgres=# select '1'::varchar - '2.1'::varchar;
?column?
----------
-1.1
(1 row)
但是使用text-text就得不到结果。
postgres=# select '1'::text - '2'::text;
ERROR: operator does not exist: text - text
LINE 1: select '1'::text - '2'::text;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
1、隐式转换方法
1、创建隐式转换的语法
postgres=# \h create cast
Command: CREATE CAST
Description: define a new cast
Syntax:
CREATE CAST (source_type AS target_type)
WITH FUNCTION function_name [ (argument_type [, ...]) ]
[ AS ASSIGNMENT | AS IMPLICIT ]
CREATE CAST (source_type AS target_type)
WITHOUT FUNCTION
[ AS ASSIGNMENT | AS IMPLICIT ]
CREATE CAST (source_type AS target_type)
WITH INOUT
[ AS ASSIGNMENT | AS IMPLICIT ]
2、创建text隐式转numeric
postgres=# create cast (text as numeric) with inout AS IMPLICIT ;
CREATE CAST
3、现在可以做减法了
postgres=# select '1'::text - '2'::text;
?column?
----------
-1
(1 row)
postgres=# select '1'::text - '2.1'::text;
?column?
----------
-1.1
(1 row)
2、创建text-text操作符方法
第二种方法是使用新建操作符的方法.
1、创建运算函数
create or replace function text_text(text,text) returns numeric as $$
select $1::numeric-$2::numeric;
$$ language sql strict immutable;
CREATE FUNCTION
测试运算函数
postgres=# select text_text('1.1', '2.222');
text_text
-----------
-1.122
(1 row)
2、基于运算函数，创建操作符
postgres=# create operator - (procedure=text_text, leftarg=text, rightarg=text);
CREATE OPERATOR
3、现在可以支持text-text了。
为了验证操作符的效果，先把前面创建的隐式转换删掉
postgres=# drop cast (text as numeric);
DROP CAST
postgres=# select '1.1'::text-'1.2'::text;
?column?
----------
-0.1
(1 row)
postgres=# select '1.1'::text-'1.22'::text;
?column?
----------
-0.12
(1 row)
