PostgreSQL自定義自動型別轉換(CAST)

標籤

PostgreSQL , cast

---

背景

PostgreSQL是一個強型別資料庫，因此你輸入的變數、常量是什麼型別，是強繫結的，例如

在呼叫操作符時，需要通過操作符邊上的資料型別，選擇對應的操作符。

在呼叫函式時，需要根據輸入的型別，選擇對應的函式。

如果型別不匹配，就會報操作符不存在，或者函式不存在的錯誤。

postgres=# select `1` + `1`;  
ERROR:  operator is not unique: unknown + unknown  
LINE 1: select `1` + `1`;  
                   ^  
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.  

那麼使用起來是不是很不方便呢？

PostgreSQL開放了型別轉換的介面，同時也內建了很多的自動型別轉換。來簡化操作。

檢視目前已有的型別轉換：

postgres=# dC+  
                                                List of casts  
         Source type         |         Target type         |      Function      |   Implicit?   | Description   
-----------------------------+-----------------------------+--------------------+---------------+-------------  
 "char"                      | character                   | bpchar             | in assignment |   
 "char"                      | character varying           | text               | in assignment |   
 "char"                      | integer                     | int4               | no            |   
 "char"                      | text                        | text               | yes           |   
 abstime                     | date                        | date               | in assignment |   
 abstime                     | integer                     | (binary coercible) | no            |   
 abstime                     | time without time zone      | time               | in assignment |   
  
 ................................  
  
 timestamp without time zone | timestamp with time zone    | timestamptz        | yes           |   
 timestamp without time zone | timestamp without time zone | timestamp          | yes           |   
 xml                         | character                   | (binary coercible) | in assignment |   
 xml                         | character varying           | (binary coercible) | in assignment |   
 xml                         | text                        | (binary coercible) | in assignment |   
(246 rows)  

如果你發現有些型別轉換沒有內建，怎麼辦呢？我們可以自定義轉換。

當然你也可以使用這種語法，對型別進行強制轉換：

CAST(x AS typename)   
  
  or   
  
x::typename  

如何自定義型別轉換(CAST)

自定義CAST的語法如下：

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 ]  

解釋：

1、WITH FUNCTION，表示轉換需要用到什麼函式。

2、WITHOUT FUNCTION，表示被轉換的兩個型別，在資料庫的儲存中一致，即物理儲存一致。例如text和varchar的物理儲存一致。不需要轉換函式。

Two types can be binary coercible,   
which means that the conversion can be performed “for free” without invoking any function.   
  
This requires that corresponding values use the same internal representation.   
  
For instance, the types text and varchar are binary coercible both ways.   
  
Binary coercibility is not necessarily a symmetric relationship.   
  
For example, the cast from xml to text can be performed for free in the present implementation,   
but the reverse direction requires a function that performs at least a syntax check.   
  
(Two types that are binary coercible both ways are also referred to as binary compatible.)  

3、WITH INOUT，表示使用內建的IO函式進行轉換。每一種型別，都有INPUT 和OUTPUT函式。使用這種方法，好處是不需要重新寫轉換函式。

除非有特殊需求，我們建議直接使用IO函式來進行轉換。

                               List of functions  
   Schema   |      Name       | Result data type | Argument data types |  Type    
------------+-----------------+------------------+---------------------+--------  
 pg_catalog | textin          | text             | cstring             | normal  
 pg_catalog | textout         | cstring          | text                | normal  
 pg_catalog | date_in         | date             | cstring             | normal  
 pg_catalog | date_out        | cstring          | date                | normal  

You can define a cast as an I/O conversion cast by using the WITH INOUT syntax.   
  
An I/O conversion cast is performed by invoking the output function of the source data type,   
and passing the resulting string to the input function of the target data type.   
  
In many common cases, this feature avoids the need to write a separate cast function for conversion.   
  
An I/O conversion cast acts the same as a regular function-based cast; only the implementation is different.  

4、AS ASSIGNMENT，表示在賦值時，自動對型別進行轉換。例如欄位型別為TEXT，輸入的型別為INT，那麼可以建立一個 cast(int as text) as ASSIGNMENT。

If the cast is marked AS ASSIGNMENT then it can be invoked implicitly when assigning a value to a column of the target data type.   
  
For example, supposing that foo.f1 is a column of type text, then:  
  
INSERT INTO foo (f1) VALUES (42);  
  
will be allowed if the cast from type integer to type text is marked AS ASSIGNMENT,   
otherwise not.   
  
(We generally use the term assignment cast to describe this kind of cast.)  

5、AS IMPLICIT，表示在表示式中，或者在賦值操作中，都對型別進行自動轉換。（包含了AS ASSIGNMENT，它只對賦值進行轉換）

If the cast is marked AS IMPLICIT then it can be invoked implicitly in any context,   
whether assignment or internally in an expression.   
  
(We generally use the term implicit cast to describe this kind of cast.)   
  
For example, consider this query:  
  
SELECT 2 + 4.0;  
  
The parser initially marks the constants as being of type integer and numeric respectively.   
  
There is no integer + numeric operator in the system catalogs, but there is a numeric + numeric operator.   
  
The query will therefore succeed if a cast from integer to numeric is available and is marked AS IMPLICIT —   
which in fact it is.   
  
The parser will apply the implicit cast and resolve the query as if it had been written  
  
SELECT CAST ( 2 AS numeric ) + 4.0;  

6、注意，AS IMPLICIT需要謹慎使用，為什麼呢？因為操作符會涉及到多個運算元，如果有多個轉換，目前資料庫並不知道應該選擇哪個？

Now, the catalogs also provide a cast from numeric to integer.   
  
If that cast were marked AS IMPLICIT — (which it is not — )  
  
then the parser would be faced with choosing between the above interpretation and   
the alternative of casting the numeric constant to integer and applying the integer + integer operator.   
  
Lacking any knowledge of which choice to prefer, it would give up and declare the query ambiguous.   
  
The fact that only one of the two casts is implicit is the way in which we teach the parser to prefer resolution of   
a mixed numeric-and-integer expression as numeric;   
  
there is no built-in knowledge about that.  

因此，建議謹慎使用AS IMPLICIT。建議使用AS IMPLICIT的CAST應該是非失真轉換轉換，例如從INT轉換為TEXT，或者int轉換為numeric。

而失真轉換，不建議使用as implicit，例如numeric轉換為int。

It is wise to be conservative about marking casts as implicit.   
  
An overabundance of implicit casting paths can cause PostgreSQL to choose surprising interpretations of commands,   
or to be unable to resolve commands at all because there are multiple possible interpretations.   
  
A good rule of thumb is to make a cast implicitly invokable only for information-preserving   
transformations between types in the same general type category.   
  
For example, the cast from int2 to int4 can reasonably be implicit,   
but the cast from float8 to int4 should probably be assignment-only.   
  
Cross-type-category casts, such as text to int4, are best made explicit-only.  

注意事項 + 例子

不能巢狀轉換。例子

1、將text轉換為date

錯誤方法

create or replace function text_to_date(text) returns date as $$  
  select cast($1 as date);  
$$ language sql strict;  
  
create cast (text as date) with function text_to_date(text) as implicit;  

巢狀轉換後出現死迴圈

postgres=# select text `2017-01-01` + 1;  
ERROR:  stack depth limit exceeded  
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform`s stack depth limit is adequate.  
CONTEXT:  SQL function "text_to_date" during startup  
SQL function "text_to_date" statement 1  
SQL function "text_to_date" statement 1  
SQL function "text_to_date" statement 1  
......  

正確方法

create or replace function text_to_date(text) returns date as $$          
  select to_date($1,`yyyy-mm-dd`);  
$$ language sql strict;  
  
create cast (text as date) with function text_to_date(text) as implicit;  

postgres=# select text `2017-01-01` + 1;  
  ?column?    
------------  
 2017-01-02  
(1 row)  

我們還可以直接使用IO函式來轉換：

postgres=# create cast (text as date) with inout as implicit;
CREATE CAST

postgres=# select text `2017-01-01` + 1;
  ?column?  
------------
 2017-01-02
(1 row)

參考

https://www.postgresql.org/docs/10/static/sql-createcast.html