<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >The PostgreSQL Type System</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.0.11 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Extending SQL" HREF="extend.html"><LINK REL="PREVIOUS" TITLE="Extending SQL" HREF="extend.html"><LINK REL="NEXT" TITLE="User-Defined Functions" HREF="xfunc.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2007-02-02T03:57:22"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.0.11 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="extend.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="extend.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 31. Extending <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="extend.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="xfunc.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="EXTEND-TYPE-SYSTEM" >31.2. The <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > Type System</A ></H1 ><A NAME="AEN29410" ></A ><A NAME="AEN29412" ></A ><A NAME="AEN29415" ></A ><A NAME="AEN29417" ></A ><P > <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > data types are divided into base types, composite types, domains, and pseudo-types. </P ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN29422" >31.2.1. Base Types</A ></H2 ><P > Base types are those, like <TT CLASS="TYPE" >int4</TT >, that are implemented below the level of the <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > language (typically in a low-level language such as C). They generally correspond to what are often known as abstract data types. <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > can only operate on such types through functions provided by the user and only understands the behavior of such types to the extent that the user describes them. Base types are further subdivided into scalar and array types. For each scalar type, a corresponding array type is automatically created that can hold variable-size arrays of that scalar type. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN29428" >31.2.2. Composite Types</A ></H2 ><P > Composite types, or row types, are created whenever the user creates a table; it's also possible to define a <SPAN CLASS="QUOTE" >"stand-alone"</SPAN > composite type with no associated table. A composite type is simply a list of base types with associated field names. A value of a composite type is a row or record of field values. The user can access the component fields from <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > queries. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN29433" >31.2.3. Domains</A ></H2 ><P > A domain is based on a particular base type and for many purposes is interchangeable with its base type. However, a domain may have constraints that restrict its valid values to a subset of what the underlying base type would allow. </P ><P > Domains can be created using the <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM > command <A HREF="sql-createdomain.html" ><I >CREATE DOMAIN</I ></A >. Their creation and use is not discussed in this chapter. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="AEN29439" >31.2.4. Pseudo-Types</A ></H2 ><P > There are a few <SPAN CLASS="QUOTE" >"pseudo-types"</SPAN > for special purposes. Pseudo-types cannot appear as columns of tables or attributes of composite types, but they can be used to declare the argument and result types of functions. This provides a mechanism within the type system to identify special classes of functions. <A HREF="datatype-pseudo.html#DATATYPE-PSEUDOTYPES-TABLE" >Table 8-20</A > lists the existing pseudo-types. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="EXTEND-TYPES-POLYMORPHIC" >31.2.5. Polymorphic Types</A ></H2 ><A NAME="AEN29446" ></A ><A NAME="AEN29448" ></A ><A NAME="AEN29450" ></A ><A NAME="AEN29453" ></A ><P > Two pseudo-types of special interest are <TT CLASS="TYPE" >anyelement</TT > and <TT CLASS="TYPE" >anyarray</TT >, which are collectively called <I CLASS="FIRSTTERM" >polymorphic types</I >. Any function declared using these types is said to be a <I CLASS="FIRSTTERM" >polymorphic function</I >. A polymorphic function can operate on many different data types, with the specific data type(s) being determined by the data types actually passed to it in a particular call. </P ><P > Polymorphic arguments and results are tied to each other and are resolved to a specific data type when a query calling a polymorphic function is parsed. Each position (either argument or return value) declared as <TT CLASS="TYPE" >anyelement</TT > is allowed to have any specific actual data type, but in any given call they must all be the <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >same</I ></SPAN > actual type. Each position declared as <TT CLASS="TYPE" >anyarray</TT > can have any array data type, but similarly they must all be the same type. If there are positions declared <TT CLASS="TYPE" >anyarray</TT > and others declared <TT CLASS="TYPE" >anyelement</TT >, the actual array type in the <TT CLASS="TYPE" >anyarray</TT > positions must be an array whose elements are the same type appearing in the <TT CLASS="TYPE" >anyelement</TT > positions. </P ><P > Thus, when more than one argument position is declared with a polymorphic type, the net effect is that only certain combinations of actual argument types are allowed. For example, a function declared as <TT CLASS="LITERAL" >equal(anyelement, anyelement)</TT > will take any two input values, so long as they are of the same data type. </P ><P > When the return value of a function is declared as a polymorphic type, there must be at least one argument position that is also polymorphic, and the actual data type supplied as the argument determines the actual result type for that call. For example, if there were not already an array subscripting mechanism, one could define a function that implements subscripting as <TT CLASS="LITERAL" >subscript(anyarray, integer) returns anyelement</TT >. This declaration constrains the actual first argument to be an array type, and allows the parser to infer the correct result type from the actual first argument's type. </P ></DIV ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="extend.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="xfunc.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Extending <ACRONYM CLASS="ACRONYM" >SQL</ACRONYM ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="extend.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >User-Defined Functions</TD ></TR ></TABLE ></DIV ></BODY ></HTML >