create type/cast/ENUM

create type：http://www.postgresql.org/docs/9.3/static/sql-createtype.html

enum:http://www.postgresql.org/docs/9.3/static/datatype-enum.html，http://www.postgresql.org/docs/9.3/static/functions-enum.html

 

digoal=# CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TYPE
digoal=# CREATE TABLE person (
digoal(#     name text,
digoal(#     current_mood mood
digoal(# );
CREATE TABLE
digoal=# INSERT INTO person VALUES ('Moe', 'happy');
INSERT 0 1
digoal=# SELECT * FROM person;
 name | current_mood 
------+--------------
 Moe  | happy
(1 row)
digoal=# select enum_first(null::mood);   
 enum_first 
------------
 sad
(1 row)
digoal=# select enum_range('ok'::mood,null);   
 enum_range 
------------
 {ok,happy}
(1 row)

 

create cast:http://www.postgresql.org/docs/9.3/static/sql-createcast.html，http://www.php100.com/manual/PostgreSQL8/sql-createcast.html

drop cast:http://www.postgresql.org/docs/9.3/static/sql-dropcast.html

 

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 ]
WITHOUT FUNCTION：
表示源数据类型和目标数据类型是二进制兼容的， 所以不需要什么函数来执行转换。
AS ASSIGNMENT：
表示转换可以在赋值环境里隐含调用。
AS IMPLICIT：
表示这个转换可以在任何环境里隐含调用。

 

 

 

digoal=# select cast(24 as text);
 text 
------
 24
(1 row)
digoal=# CREATE OR REPLACE FUNCTION increment(i integer) RETURNS text AS $$
digoal$#         BEGIN
digoal$#                 RETURN 'aaaaa'||i;
digoal$#         END;
digoal$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
digoal=# CREATE CAST (int AS text) WITH FUNCTION increment(int) AS ASSIGNMENT;                    
CREATE CAST
digoal=# select cast(24 as text);
  text   
---------
 aaaaa24
(1 row)
digoal=# drop cast (int as text);
DROP CAST
digoal=# select cast(24 as text);
 text 
------
 24
(1 row)
digoal=# select 2>=cast(24 as text)||''; 
ERROR:  operator does not exist: integer >= text
LINE 1: select 2>=cast(24 as text)||'';
                ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
digoal=# CREATE CAST (int AS text) WITH FUNCTION increment(int) AS IMPLICIT;
CREATE CAST
digoal=# select 2>=cast(24 as text)||'';                                    
 ?column? 
----------
 false
(1 row)