Adding upstream version 15.5.upstream/15.5

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
author: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-05-04 12:17:33 +0000
committer: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-05-04 12:17:33 +0000
commit: 5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree: 739caf8c461053357daa9f162bef34516c7bf452 /doc/src/sgml/html/sql-createcast.html
parent: Initial commit. (diff)
download: postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz
postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip
1 files changed, 256 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-createcast.html b/doc/src/sgml/html/sql-createcast.html
new file mode 100644
index 0000000..cc91355
--- /dev/null
+++ b/doc/src/sgml/html/sql-createcast.html
@@ -0,0 +1,256 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE CAST</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createaggregate.html" title="CREATE AGGREGATE" /><link rel="next" href="sql-createcollation.html" title="CREATE COLLATION" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE CAST</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createaggregate.html" title="CREATE AGGREGATE">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createcollation.html" title="CREATE COLLATION">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATECAST"><div class="titlepage"></div><a id="id-1.9.3.58.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE CAST</span></h2><p>CREATE CAST — define a new cast</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
+CREATE CAST (<em class="replaceable"><code>source_type</code></em> AS <em class="replaceable"><code>target_type</code></em>)
+    WITH FUNCTION <em class="replaceable"><code>function_name</code></em> [ (<em class="replaceable"><code>argument_type</code></em> [, ...]) ]
+    [ AS ASSIGNMENT | AS IMPLICIT ]
+
+CREATE CAST (<em class="replaceable"><code>source_type</code></em> AS <em class="replaceable"><code>target_type</code></em>)
+    WITHOUT FUNCTION
+    [ AS ASSIGNMENT | AS IMPLICIT ]
+
+CREATE CAST (<em class="replaceable"><code>source_type</code></em> AS <em class="replaceable"><code>target_type</code></em>)
+    WITH INOUT
+    [ AS ASSIGNMENT | AS IMPLICIT ]
+</pre></div><div class="refsect1" id="SQL-CREATECAST-DESCRIPTION"><h2>Description</h2><p>
+   <code class="command">CREATE CAST</code> defines a new cast.  A cast
+   specifies how to perform a conversion between
+   two data types.  For example,
+</p><pre class="programlisting">
+SELECT CAST(42 AS float8);
+</pre><p>
+   converts the integer constant 42 to type <code class="type">float8</code> by
+   invoking a previously specified function, in this case
+   <code class="literal">float8(int4)</code>. (If no suitable cast has been defined, the
+   conversion fails.)
+  </p><p>
+   Two types can be <em class="firstterm">binary coercible</em>, which
+   means that the conversion can be performed <span class="quote">“<span class="quote">for free</span>”</span>
+   without invoking any function.  This requires that corresponding
+   values use the same internal representation.  For instance, the
+   types <code class="type">text</code> and <code class="type">varchar</code> are binary
+   coercible both ways.  Binary coercibility is not necessarily a
+   symmetric relationship.  For example, the cast
+   from <code class="type">xml</code> to <code class="type">text</code> 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.)
+  </p><p>
+   You can define a cast as an <em class="firstterm">I/O conversion cast</em> by using
+   the <code class="literal">WITH INOUT</code> syntax. An I/O conversion cast is
+   performed by invoking the output function of the source data type, and
+   passing the resulting string to the input function of the target data type.
+   In many common cases, this feature avoids the need to write a separate
+   cast function for conversion. An I/O conversion cast acts the same as
+   a regular function-based cast; only the implementation is different.
+  </p><p>
+   By default, a cast can be invoked only by an explicit cast request,
+   that is an explicit <code class="literal">CAST(<em class="replaceable"><code>x</code></em> AS
+   <em class="replaceable"><code>typename</code></em>)</code> or
+   <em class="replaceable"><code>x</code></em><code class="literal">::</code><em class="replaceable"><code>typename</code></em>
+   construct.
+  </p><p>
+   If the cast is marked <code class="literal">AS ASSIGNMENT</code> then it can be invoked
+   implicitly when assigning a value to a column of the target data type.
+   For example, supposing that <code class="literal">foo.f1</code> is a column of
+   type <code class="type">text</code>, then:
+</p><pre class="programlisting">
+INSERT INTO foo (f1) VALUES (42);
+</pre><p>
+   will be allowed if the cast from type <code class="type">integer</code> to type
+   <code class="type">text</code> is marked <code class="literal">AS ASSIGNMENT</code>, otherwise not.
+   (We generally use the term <em class="firstterm">assignment
+   cast</em> to describe this kind of cast.)
+  </p><p>
+   If the cast is marked <code class="literal">AS IMPLICIT</code> then it can be invoked
+   implicitly in any context, whether assignment or internally in an
+   expression.  (We generally use the term <em class="firstterm">implicit
+   cast</em> to describe this kind of cast.)
+   For example, consider this query:
+</p><pre class="programlisting">
+SELECT 2 + 4.0;
+</pre><p>
+   The parser initially marks the constants as being of type <code class="type">integer</code>
+   and <code class="type">numeric</code> respectively.  There is no <code class="type">integer</code>
+   <code class="literal">+</code> <code class="type">numeric</code> operator in the system catalogs,
+   but there is a <code class="type">numeric</code> <code class="literal">+</code> <code class="type">numeric</code> operator.
+   The query will therefore succeed if a cast from <code class="type">integer</code> to
+   <code class="type">numeric</code> is available and is marked <code class="literal">AS IMPLICIT</code> —
+   which in fact it is.  The parser will apply the implicit cast and resolve
+   the query as if it had been written
+</p><pre class="programlisting">
+SELECT CAST ( 2 AS numeric ) + 4.0;
+</pre><p>
+  </p><p>
+   Now, the catalogs also provide a cast from <code class="type">numeric</code> to
+   <code class="type">integer</code>.  If that cast were marked <code class="literal">AS IMPLICIT</code> —
+   which it is not — then the parser would be faced with choosing
+   between the above interpretation and the alternative of casting the
+   <code class="type">numeric</code> constant to <code class="type">integer</code> and applying the
+   <code class="type">integer</code> <code class="literal">+</code> <code class="type">integer</code> 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 <code class="type">numeric</code>-and-<code class="type">integer</code> expression as
+   <code class="type">numeric</code>; there is no built-in knowledge about that.
+  </p><p>
+   It is wise to be conservative about marking casts as implicit.  An
+   overabundance of implicit casting paths can cause
+   <span class="productname">PostgreSQL</span> 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 <code class="type">int2</code> to
+   <code class="type">int4</code> can reasonably be implicit, but the cast from
+   <code class="type">float8</code> to <code class="type">int4</code> should probably be
+   assignment-only.  Cross-type-category casts, such as <code class="type">text</code>
+   to <code class="type">int4</code>, are best made explicit-only.
+  </p><div class="note"><h3 class="title">Note</h3><p>
+    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 <em class="firstterm">type categories</em> and <em class="firstterm">preferred
+    types</em> that can help to provide desired behavior in such cases.  See
+    <a class="xref" href="sql-createtype.html" title="CREATE TYPE"><span class="refentrytitle">CREATE TYPE</span></a> for
+    more information.
+   </p></div><p>
+   To be able to create a cast, you must own the source or the target data type
+   and have <code class="literal">USAGE</code> privilege on the other 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.)
+  </p></div><div class="refsect1" id="id-1.9.3.58.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>source_type</code></em></span></dt><dd><p>
+       The name of the source data type of the cast.
+      </p></dd><dt><span class="term"><em class="replaceable"><code>target_type</code></em></span></dt><dd><p>
+       The name of the target data type of the cast.
+      </p></dd><dt><span class="term"><code class="literal"><em class="replaceable"><code>function_name</code></em>[(<em class="replaceable"><code>argument_type</code></em> [, ...])]</code></span></dt><dd><p>
+       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.
+       If no argument list is specified, the function name must be unique in
+       its schema.
+      </p></dd><dt><span class="term"><code class="literal">WITHOUT FUNCTION</code></span></dt><dd><p>
+       Indicates that the source type is binary-coercible to the target type,
+       so no function is required to perform the cast.
+      </p></dd><dt><span class="term"><code class="literal">WITH INOUT</code></span></dt><dd><p>
+       Indicates that the cast is an I/O conversion cast, performed by
+       invoking the output function of the source data type, and passing the
+       resulting string to the input function of the target data type.
+      </p></dd><dt><span class="term"><code class="literal">AS ASSIGNMENT</code></span></dt><dd><p>
+       Indicates that the cast can be invoked implicitly in assignment
+       contexts.
+      </p></dd><dt><span class="term"><code class="literal">AS IMPLICIT</code></span></dt><dd><p>
+       Indicates that the cast can be invoked implicitly in any context.
+      </p></dd></dl></div><p>
+   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 <code class="type">integer</code>; it receives the type
+   modifier associated with the destination type, or <code class="literal">-1</code>
+   if there is none.  The third argument,
+   if present, must be type <code class="type">boolean</code>; it receives <code class="literal">true</code>
+   if the cast is an explicit cast, <code class="literal">false</code> 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.)
+  </p><p>
+   The return type of a cast function must be identical to or
+   binary-coercible to the cast's target type.
+  </p><p>
+   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.
+  </p><p>
+   When a cast has different source and
+   target types and a function that takes more than one argument, it
+   supports 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 cast steps, one to
+   convert between data types and a second to apply the modifier.
+  </p><p>
+   A cast to or from a domain type currently has no effect.  Casting
+   to or from a domain uses the casts associated with its underlying type.
+  </p></div><div class="refsect1" id="SQL-CREATECAST-NOTES"><h2>Notes</h2><p>
+   Use <a class="link" href="sql-dropcast.html" title="DROP CAST"><code class="command">DROP CAST</code></a> to remove user-defined casts.
+  </p><p>
+   Remember that if you want to be able to convert types both ways you
+   need to declare casts both ways explicitly.
+  </p><a id="id-1.9.3.58.7.4" class="indexterm"></a><p>
+   It is normally not necessary to create casts between user-defined types
+   and the standard string types (<code class="type">text</code>, <code class="type">varchar</code>, and
+   <code class="type">char(<em class="replaceable"><code>n</code></em>)</code>, as well as user-defined types that
+   are defined to be in the string category).  <span class="productname">PostgreSQL</span>
+   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.)
+  </p><p>
+   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
+   <em class="replaceable"><code>typename</code></em>(<em class="replaceable"><code>x</code></em>).  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 <span class="productname">PostgreSQL</span> 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.
+  </p><div class="note"><h3 class="title">Note</h3><p>
+    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 <em class="replaceable"><code>name</code></em>(<em class="replaceable"><code>x</code></em>) does not
+    exactly match any existing function, but <em class="replaceable"><code>name</code></em> is the name
+    of a data type and <code class="structname">pg_cast</code> provides a binary-coercible cast
+    to this type from the type of <em class="replaceable"><code>x</code></em>, 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
+    <code class="structname">pg_cast</code> 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.
+   </p></div><div class="note"><h3 class="title">Note</h3><p>
+    There is also an exception to the exception: I/O conversion casts from
+    composite types to string types cannot be invoked using functional
+    syntax, but must be written in explicit cast syntax (either
+    <code class="literal">CAST</code> or <code class="literal">::</code> notation).  This exception was added
+    because after the introduction of automatically-provided I/O conversion
+    casts, it was found too easy to accidentally invoke such a cast when
+    a function or column reference was intended.
+   </p></div></div><div class="refsect1" id="SQL-CREATECAST-EXAMPLES"><h2>Examples</h2><p>
+   To create an assignment cast from type <code class="type">bigint</code> to type
+   <code class="type">int4</code> using the function <code class="literal">int4(bigint)</code>:
+</p><pre class="programlisting">
+CREATE CAST (bigint AS int4) WITH FUNCTION int4(bigint) AS ASSIGNMENT;
+</pre><p>
+   (This cast is already predefined in the system.)
+  </p></div><div class="refsect1" id="SQL-CREATECAST-COMPAT"><h2>Compatibility</h2><p>
+   The <code class="command">CREATE CAST</code> command conforms to the
+   <acronym class="acronym">SQL</acronym> standard,
+   except that SQL does not make provisions for binary-coercible
+   types or extra arguments to implementation functions.
+   <code class="literal">AS IMPLICIT</code> is a <span class="productname">PostgreSQL</span>
+   extension, too.
+  </p></div><div class="refsect1" id="SQL-CREATECAST-SEEALSO"><h2>See Also</h2><p>
+   <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a>,
+   <a class="xref" href="sql-createtype.html" title="CREATE TYPE"><span class="refentrytitle">CREATE TYPE</span></a>,
+   <a class="xref" href="sql-dropcast.html" title="DROP CAST"><span class="refentrytitle">DROP CAST</span></a>
+  </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createaggregate.html" title="CREATE AGGREGATE">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createcollation.html" title="CREATE COLLATION">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE AGGREGATE </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE COLLATION</td></tr></table></div></body></html>
+\ No newline at end of file
author	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-05-04 12:17:33 +0000
committer	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-05-04 12:17:33 +0000
commit	5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree	739caf8c461053357daa9f162bef34516c7bf452 /doc/src/sgml/html/sql-createcast.html
parent	Initial commit. (diff)
download	postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip