有哪些做鞋机设备的网站/关键词优化推广-专业网站定制开发

背景

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)