CREATE 
CAST(7)							   SQL Commands 						    CREATE CAST(7)

NAME

       CREATE CAST - define a user-defined cast

SYNOPSIS

       CREATE CAST (sourcetype AS targettype)
	   WITH FUNCTION funcname (argtype)
	   [ AS ASSIGNMENT | AS IMPLICIT ]

       CREATE CAST (sourcetype AS targettype)
	   WITHOUT FUNCTION
	   [ AS ASSIGNMENT | AS IMPLICIT ]

DESCRIPTION

       CREATE CAST defines a new cast. A cast specifies how to perform a conversion between two data types. For example,

       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 ``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 compati-
       ble.

       By default, a cast can be invoked only by an explicit cast request, that is an explicit CAST(x AS typename),  x::typename,  or  typename(x)
       construct.

       If  the	cast  is  marked AS ASSIGNMENT then it can be invoked implicitly when assigning 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. For
       example, since || takes text arguments,

       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

       SELECT 'The time is ' || CAST(now() AS text);

       (We generally use the term implicit cast to describe this kind of cast.)

       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.

       To  be  able  to  create a cast, you must own the source or the target data type. To create a binary-compatible cast, you must be superuser
       (this restriction is made because an erroneous binary-compatible cast conversion can easily crash the server).  "PARAMETERS"

       sourcetype
	      The name of the source data type of the cast.

       targettype
	      The name of the target data type of the cast.

       funcname(argtype)
	      The function used to perform the cast. The function name may be schema-qualified. If it is not, the function will be  looked  up	in
	      the  path. The argument type must be identical to the source type, the result data type must match the target type of the cast. Cast
	      functions must be marked immutable or stable.

       WITHOUT FUNCTION
	      Indicates that the source type and the target type are binary compatible, so no function is required to perform the cast.

       AS ASSIGNMENT
	      Indicates that the cast may be invoked implicitly in assignment contexts.

       AS IMPLICIT
	      Indicates that the cast may be invoked implicitly in any context.

NOTES

       Use DROP CAST to remove user-defined casts.

       Remember that if you want to be able to convert types both ways you need to declare casts both ways explicitly.

       Prior to PostgreSQL 7.3, every function that had the same name as a data type, returned that data type, and took one argument of a  differ-
       ent  type  was  automatically  a cast function. This convention has been abandoned in face of the introduction of schemas and to be able to
       represent binary compatible casts in the catalogs. (The built-in cast functions still follow this naming scheme, but they have to be  shown
       as casts in pg_cast now.)

EXAMPLES

       To create a cast from type text to type int4 using the function int4(text):

       CREATE CAST (text AS int4) WITH FUNCTION int4(text);

       (This cast is already predefined in the system.)

COMPATIBILITY

       The  CREATE  CAST command conforms to SQL99, except that SQL99 does not make provisions for binary compatible types. AS IMPLICIT is a Post-
       greSQL extension, too.

SEE ALSO

       CREATE FUNCTION [create_function(7)], CREATE TYPE [create_type(7)], DROP CAST [drop_cast(7)], PostgreSQL Programmer's Guide

SQL - Language Statements					    2002-11-22							    CREATE CAST(7)