


Defines a new cast.


CREATE CAST (sourcetype AS targettype) 
       WITH FUNCTION funcname (argtypes) 

CREATE CAST (sourcetype AS targettype) WITHOUT FUNCTION 



SELECT CAST(42 AS text);

converts the integer constant 42 to type text by invoking a previously specified function, in this case text(int4). If no suitable cast has been defined, the conversion fails.

Two types may be binary compatible, which means that they can be converted into one another without invoking any function. This requires that corresponding values use the same internal representation. For instance, the types text and varchar are binary compatible.



INSERT INTO foo (f1) VALUES (42);

will be allowed if the cast from type integer to type text is marked AS ASSIGNMENT, otherwise not. The term assignment cast is typically used 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. The term implicit cast is typically used to describe this kind of cast. For example, since || takes text operands,

SELECT 'The time is ' || now();

will be allowed only if the cast from type timestamp to text is marked AS IMPLICIT. Otherwise, it will be necessary to write the cast explicitly, for example





The name of the source data type of the cast.
The name of the target data type of the cast.
Indicates that the cast may be invoked implicitly in assignment contexts.
Indicates that the cast may be invoked implicitly in any context.


Note that in this release of Greenplum Database, user-defined functions used in a user-defined cast must be defined as IMMUTABLE. Any compiled code (shared library files) for custom functions must be placed in the same location on every host in your Greenplum Database array (master and all segments). This location must also be in the LD_LIBRARY_PATH so that the server can locate the files.


It is recommended that you follow the convention of naming cast implementation functions after the target data type, as the built-in cast implementation functions are named. Many users are used to being able to cast data types using a function-style notation, that is typename(x).


To create a cast from type text to type int4 using the function int4(text) (This cast is already predefined in the system.):



The CREATE CAST command conforms to the SQL standard, except that SQL does not make provisions for binary-compatible types or extra arguments to implementation functions. AS IMPLICIT is a Greenplum Database extension, too.