PostgreSQL Oracle相容性之 - text - text隐式轉換

标簽

PostgreSQL , Oracle , 相容性 , text減text操作符

https://github.com/digoal/blog/blob/master/201805/20180517_04.md#%E8%83%8C%E6%99%AF 背景

Oracle的兩個文本詳見，會自動轉換為數值進行相減操作。

PostgreSQL預設并不會将文本轉換為數值進行相減操作。

為了實作相容，有兩種方法：

1、建立text到numeric的隐式轉換

2、建立text-text的操作符。

https://github.com/digoal/blog/blob/master/201805/20180517_04.md#postgresql-%E5%86%85%E7%BD%AEcast 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.  
           

https://github.com/digoal/blog/blob/master/201805/20180517_04.md#1%E9%9A%90%E5%BC%8F%E8%BD%AC%E6%8D%A2%E6%96%B9%E6%B3%95 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)  
           

https://github.com/digoal/blog/blob/master/201805/20180517_04.md#2%E5%88%9B%E5%BB%BAtext-text%E6%93%8D%E4%BD%9C%E7%AC%A6%E6%96%B9%E6%B3%95 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)  
           

https://github.com/digoal/blog/blob/master/201805/20180517_04.md#%E5%8F%82%E8%80%83 參考

《PostgreSQL 整型int與布爾boolean的自動轉換設定(含自定義cast與cast規則介紹)》 《PostgreSQL 自定義自動類型轉換(CAST)》