PostgreSQL 自定義自動類型轉換操作(CAST)

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. 

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 

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 ] 

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.) 

        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. 

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.) 

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; 

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. 

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. 

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) 

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)

postgres=# create table cas_test(id int, c1 boolean); 
CREATE TABLE 
 
postgres=# \set VERBOSITY verbose 
postgres=# insert into cas_test values (1, int '1'); 
ERROR: 42804: column "c1" is of type boolean but expression is of type integer 
LINE 1: insert into cas_test values (1, int '1'); 
           ^ 
HINT: You will need to rewrite or cast the expression. 
LOCATION: transformAssignedExpr, parse_target.c:591 

postgres=# create table tbl123(id int, crt_time timestamp); 
CREATE TABLE 
 
postgres=# insert into tbl123 values (1, text '2017-01-01 10:00:00'); 
ERROR: column "crt_time" is of type timestamp without time zone but expression is of type text 
LINE 1: insert into tbl123 values (1, text '2017-01-01 10:00:00'); 
           ^ 
HINT: You will need to rewrite or cast the expression. 

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 ] 

postgres=# \dC 
            List of casts 
   Source type   |   Target type   |   Function   | Implicit?  
-----------------------------+-----------------------------+---------------------------+--------------- 
 abstime      | date      | date      | in assignment 
 abstime      | integer      | (binary coercible)  | no 
 abstime      | timestamp without time zone | timestamp     | yes 
 ........ 
 integer      | boolean      | bool      | no 

postgres=# \dC 
            List of casts 
   Source type   |   Target type   |   Function   | Implicit?  
-----------------------------+-----------------------------+---------------------------+--------------- 
 integer      | boolean      | bool      | no 

postgres=# select cast ((int '1') as boolean); 
 bool 
------ 
 t 
(1 row) 

postgres=# update pg_cast set castcontext='a' where castsource ='integer'::regtype and casttarget='boolean'::regtype; 
UPDATE 1 

\dC 
            List of casts 
   Source type   |   Target type   |   Function   | Implicit?  
-----------------------------+-----------------------------+---------------------------+--------------- 
 integer      | boolean      | bool      | in assignment 

postgres=# create table cas_test(id int, c1 boolean); 
CREATE TABLE 
postgres=# insert into cas_test values (1, int '1'); 
INSERT 0 1 

postgres=# create cast (text as timestamp) with inout as ASSIGNMENT; 
CREATE CAST 
 
            List of casts 
   Source type   |   Target type   |   Function   | Implicit?  
-----------------------------+-----------------------------+---------------------------+--------------- 
 text      | timestamp without time zone | (binary coercible)  | in assignment 

postgres=# insert into tbl123 values (1, text '2017-01-01 10:00:00'); 
INSERT 0 1 
postgres=# select * from tbl123; 
 id |  crt_time   
----+--------------------- 
 1 | 2017-01-01 10:00:00 
(1 row) 

postgres=# drop cast (text as timestamp); 
DROP CAST 
postgres=# insert into tbl123 values (1, text '2017-01-01 10:00:00'); 
ERROR: column "crt_time" is of type timestamp without time zone but expression is of type text 
LINE 1: insert into tbl123 values (1, text '2017-01-01 10:00:00'); 
           ^ 
HINT: You will need to rewrite or cast the expression. 

postgres=# create or replace function cast_text_to_timestamp(text) returns timestamptz as $$ 
 select to_timestamp($1, 'yyyy-mm-dd hh24:mi:ss'); 
$$ language sql strict ; 
CREATE FUNCTION 

postgres=# create cast (text as timestamptz) with function cast_text_to_timestamp as ASSIGNMENT; 
CREATE CAST 
 
postgres=# \dC 
            List of casts 
   Source type   |   Target type   |   Function   | Implicit?  
-----------------------------+-----------------------------+---------------------------+--------------- 
 text      | timestamp with time zone | cast_text_to_timestamp | in assignment 

postgres=# create table tbl1234(id int, crt_time timestamptz); 
CREATE TABLE 
postgres=# insert into tbl1234 values (1, text '2017-01-01 10:10:10'); 
INSERT 0 1