create_cast

CREATE CAST()			 SQL Commands			CREATE CAST()



NAME
       CREATE CAST - define a new 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 con-
       version 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	 con-
       verted  into  one  another ‘‘for free’’ without invoking any function.
       This requires that corresponding values use the same  internal  repre-
       sentation. For instance, the types text and varchar are binary compat-
       ible.

       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 a value to a column of the target data type.  For exam-
       ple, 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 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 explic-
       itly, 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	 gen-
       eral  type  category. For example, the cast from int2 to int4 can rea-
       sonably 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.

       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 con-
	      text.

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 system catalogs. (The built-
       in cast functions still follow this naming scheme, but they have to be
       shown as casts in the system catalog 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)]



SQL - Language Statements	  2008-01-03			CREATE CAST()