赞
踩
PostgreSQL , Oracle , 兼容性 , text减text操作符
Oracle的两个文本详见,会自动转换为数值进行相减操作。
PostgreSQL默认并不会将文本转换为数值进行相减操作。
为了实现兼容,有两种方法:
1、创建text到numeric的隐式转换
2、创建text-text的操作符。
可以看到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、创建隐式转换的语法
- 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)
第二种方法是使用新建操作符的方法.
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)
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。