Provided by: postgresql-client-8.4_8.4.11-1_amd64 bug

NAME

       CREATE CAST - define a new cast

SYNOPSIS

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

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

       CREATE CAST (sourcetype AS targettype)
           WITH INOUT
           [ 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 float8);

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

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

       You can define a cast as an I/O conversion cast  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 result to the input function of the target data type.

       By default, a cast can be invoked only by an explicit cast request, that  is  an  explicit
       CAST(x AS typename) or x::typename 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 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.

              Note:  Sometimes  it  is necessary for usability or standards-compliance reasons to
              provide multiple implicit casts among a set of types, resulting in  ambiguity  that
              cannot  be  avoided  as  above.  The  parser has a fallback heuristic based on type
              categories and preferred types that can help to provide desired  behavior  in  such
              cases. See CREATE TYPE [create_type(7)] for more information.

       To  be able to create a cast, you must own the source or the target data type. To create a
       binary-coercible cast, you must be  superuser.   (This  restriction  is  made  because  an
       erroneous binary-coercible 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(argtypes)
              The  function  used to perform the cast. The function name can be schema-qualified.
              If it is not, the function will be  looked  up  in  the  schema  search  path.  The
              function's  result  data type must match the target type of the cast. Its arguments
              are discussed below.

       WITHOUT FUNCTION
              Indicates that the source type is  binary-coercible  to  the  target  type,  so  no
              function is required to perform the cast.

       WITH INOUT
              Indicates that the cast is an I/O conversion cast, performed by invoking the output
              function of the source data type, and passing the result to the input  function  of
              the target data type.

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

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

       Cast  implementation  functions  can have one to three arguments.  The first argument type
       must be identical to or binary-coercible from the cast's source type. The second argument,
       if  present,  must  be  type  integer;  it  receives the type modifier associated with the
       destination type, or -1 if there is none. The third argument, if  present,  must  be  type
       boolean;  it  receives true if the cast is an explicit cast, false otherwise.  (Bizarrely,
       the SQL standard demands different behaviors for  explicit  and  implicit  casts  in  some
       cases.  This  argument is supplied for functions that must implement such casts. It is not
       recommended that you design your own data types so that this matters.)

       The return type of a cast function must be identical to or binary-coercible to the  cast's
       target type.

       Ordinarily  a  cast  must  have  different  source  and target data types.  However, it is
       allowed to declare a cast with identical  source  and  target  types  if  it  has  a  cast
       implementation  function  with  more  than  one  argument. This is used to represent type-
       specific length coercion functions in the system catalogs. The named function is  used  to
       coerce a value of the type to the type modifier value given by its second argument.

       When  a cast has different source and target types and a function that takes more than one
       argument, it represents converting from one type to another and applying a length coercion
       in  a  single  step.  When no such entry is available, coercion to a type that uses a type
       modifier involves two steps, one to convert between data types and a second to  apply  the
       modifier.

NOTES

       Use DROP CAST [drop_cast(7)] 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.

       It is normally not necessary to create casts between user-defined types and  the  standard
       string  types  (text, varchar, and char(n), as well as user-defined types that are defined
       to be in the string category). PostgreSQL provides  automatic  I/O  conversion  casts  for
       that.  The  automatic  casts  to  string  types are treated as assignment casts, while the
       automatic casts from string types are explicit-only. You can  override  this  behavior  by
       declaring your own cast to replace an automatic cast, but usually the only reason to do so
       is if you want the conversion to be more easily invokable than  the  standard  assignment-
       only  or explicit-only setting. Another possible reason is that you want the conversion to
       behave differently from the type's I/O function; but that is sufficiently surprising  that
       you  should  think  twice  about whether it's a good idea. (A small number of the built-in
       types do indeed have different behaviors for conversions, mostly because  of  requirements
       of the SQL standard.)

       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  different  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-coercible 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 as well.

       While not required, it is recommended that you continue to follow this old  convention  of
       naming  cast  implementation  functions after the target data type. Many users are used to
       being able to cast data types using a function-style notation, that is  typename(x).  This
       notation is in fact nothing more nor less than a call of the cast implementation function;
       it is not specially treated as a cast. If your  conversion  functions  are  not  named  to
       support  this  convention  then  you  will  have surprised users.  Since PostgreSQL allows
       overloading of the  same  function  name  with  different  argument  types,  there  is  no
       difficulty  in  having multiple conversion functions from different types that all use the
       target type's name.

              Note: Actually the preceding paragraph is  an  oversimplification:  there  are  two
              cases  in which a function-call construct will be treated as a cast request without
              having matched it to an actual function.  If  a  function  call  name(x)  does  not
              exactly  match  any  existing  function,  but  name  is the name of a data type and
              pg_cast provides a binary-coercible cast to this type from the type of x, then  the
              call  will  be construed as a binary-coercible cast. This exception is made so that
              binary-coercible casts can be invoked using functional  syntax,  even  though  they
              lack  any function. Likewise, if there is no pg_cast entry but the cast would be to
              or from a string type, the call will be construed as an I/O conversion  cast.  This
              exception allows I/O conversion casts to be invoked using functional syntax.

EXAMPLES

       To  create  an  assignment  cast  from  type  bigint  to  type  int4  using  the  function
       int4(bigint):

       CREATE CAST (bigint AS int4) WITH FUNCTION int4(bigint) AS ASSIGNMENT;

       (This cast is already predefined in the system.)

COMPATIBILITY

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

SEE ALSO

       CREATE  FUNCTION   [create_function(7)],   CREATE   TYPE   [create_type(7)],   DROP   CAST
       [drop_cast(7)]