diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/catalogs.sgml | 77 | ||||
| -rw-r--r-- | doc/src/sgml/datatype.sgml | 16 | ||||
| -rw-r--r-- | doc/src/sgml/extend.sgml | 22 | ||||
| -rw-r--r-- | doc/src/sgml/filelist.sgml | 1 | ||||
| -rw-r--r-- | doc/src/sgml/func.sgml | 304 | ||||
| -rw-r--r-- | doc/src/sgml/plpgsql.sgml | 6 | ||||
| -rw-r--r-- | doc/src/sgml/rangetypes.sgml | 373 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_type.sgml | 61 | ||||
| -rw-r--r-- | doc/src/sgml/xfunc.sgml | 6 |
9 files changed, 850 insertions, 16 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index cfecaa6931a..2063812942f 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -219,6 +219,11 @@ </row> <row> + <entry><link linkend="catalog-pg-range"><structname>pg_range</structname></link></entry> + <entry>information about range types</entry> + </row> + + <row> <entry><link linkend="catalog-pg-rewrite"><structname>pg_rewrite</structname></link></entry> <entry>query rewrite rules</entry> </row> @@ -4594,6 +4599,78 @@ </sect1> + <sect1 id="catalog-pg-range"> + <title><structname>pg_range</structname></title> + + <indexterm zone="catalog-pg-range"> + <primary>pg_range</primary> + </indexterm> + + <para> + The catalog <structname>pg_range</structname> stores information about range types. + </para> + + <table> + <title><structname>pg_range</> Columns</title> + + <tgroup cols="4"> + <thead> + <row> + <entry>Name</entry> + <entry>Type</entry> + <entry>References</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><structfield>rngtypid</structfield></entry> + <entry><type>oid</type></entry> + <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry> + <entry>The type that is a range type</entry> + </row> + + <row> + <entry><structfield>rngsubtype</structfield></entry> + <entry><type>oid</type></entry> + <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry> + <entry>Subtype of this range type, e.g. <type>integer</type> is the subtype of <type>int4range</type></entry> + </row> + + <row> + <entry><structfield>rngcollation</structfield></entry> + <entry><type>oid</type></entry> + <entry><literal><link linkend="catalog-pg-collation"><structname>pg_collation</structname></link>.oid</literal></entry> + <entry>The collation used when comparing range boundaries</entry> + </row> + + <row> + <entry><structfield>rngsubopc</structfield></entry> + <entry><type>oid</type></entry> + <entry><literal><link linkend="catalog-pg-opclass"><structname>pg_opclass</structname></link>.oid</literal></entry> + <entry>The operator class used when comparing range boundaries</entry> + </row> + + <row> + <entry><structfield>rngcanonical</structfield></entry> + <entry><type>regproc</type></entry> + <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry> + <entry>A function to convert a range into its canonical form</entry> + </row> + + <row> + <entry><structfield>rngsubdiff</structfield></entry> + <entry><type>regproc</type></entry> + <entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry> + <entry>A function to return the distance between two lower and upper bound, as a <type>double precision</type>. Used for GiST support</entry> + </row> + </tbody> + </tgroup> + </table> + + </sect1> + <sect1 id="catalog-pg-rewrite"> <title><structname>pg_rewrite</structname></title> diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index e7b3098f28f..fe59a1c7763 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -4173,6 +4173,8 @@ SET xmloption TO { DOCUMENT | CONTENT }; &rowtypes; + &rangetypes; + <sect1 id="datatype-oid"> <title>Object Identifier Types</title> @@ -4444,6 +4446,10 @@ SELECT * FROM pg_attribute </indexterm> <indexterm zone="datatype-pseudo"> + <primary>anyrange</primary> + </indexterm> + + <indexterm zone="datatype-pseudo"> <primary>void</primary> </indexterm> @@ -4520,6 +4526,13 @@ SELECT * FROM pg_attribute </row> <row> + <entry><type>anyrange</></entry> + <entry>Indicates that a function accepts any range data type + (see <xref linkend="extend-types-polymorphic"> and + <xref linkend="rangetypes">).</entry> + </row> + + <row> <entry><type>anynonarray</></entry> <entry>Indicates that a function accepts any non-array data type (see <xref linkend="extend-types-polymorphic">).</entry> @@ -4583,7 +4596,8 @@ SELECT * FROM pg_attribute only <type>void</> and <type>record</> as a result type (plus <type>trigger</> when the function is used as a trigger). Some also support polymorphic functions using the types <type>anyarray</>, - <type>anyelement</>, <type>anyenum</>, and <type>anynonarray</>. + <type>anyelement</>, <type>anyenum</>, <type>anyrange</>, and + <type>anynonarray</>. </para> <para> diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml index 7079db3ed3f..f3850b391e0 100644 --- a/doc/src/sgml/extend.sgml +++ b/doc/src/sgml/extend.sgml @@ -198,14 +198,15 @@ </indexterm> <para> - Four pseudo-types of special interest are <type>anyelement</>, - <type>anyarray</>, <type>anynonarray</>, and <type>anyenum</>, - which are collectively called <firstterm>polymorphic types</>. - Any function declared using these types is said to be - a <firstterm>polymorphic function</>. 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. + Five pseudo-types of special interest are <type>anyelement</>, + <type>anyarray</>, <type>anynonarray</>, <type>anyenum</>, + and <type>anyrange</>, which are collectively + called <firstterm>polymorphic types</>. Any function declared + using these types is said to be a <firstterm>polymorphic + function</>. 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. </para> <para> @@ -221,6 +222,11 @@ <type>anyelement</type>, the actual array type in the <type>anyarray</type> positions must be an array whose elements are the same type appearing in the <type>anyelement</type> positions. + Similarly, if there are positions declared <type>anyrange</type> + and others declared + <type>anyelement</type>, the actual range type in the + <type>anyrange</type> positions must be a range whose subtype is + the same type appearing in the <type>anyelement</type> positions. <type>anynonarray</> is treated exactly the same as <type>anyelement</>, but adds the additional constraint that the actual type must not be an array type. diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index ed39e0b6612..fb69415f800 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -25,6 +25,7 @@ <!ENTITY mvcc SYSTEM "mvcc.sgml"> <!ENTITY perform SYSTEM "perform.sgml"> <!ENTITY queries SYSTEM "queries.sgml"> +<!entity rangetypes SYSTEM "rangetypes.sgml"> <!ENTITY rowtypes SYSTEM "rowtypes.sgml"> <!ENTITY syntax SYSTEM "syntax.sgml"> <!ENTITY textsearch SYSTEM "textsearch.sgml"> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 2b8298c3e0c..f81bb9db977 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10457,6 +10457,310 @@ SELECT NULLIF(value, '(none)') ... </para> </sect1> + <sect1 id="functions-range"> + <title>Range Functions and Operators</title> + + <para> + <xref linkend="range-operators-table"> shows the operators + available for range types. + </para> + + <table id="range-operators-table"> + <title>Range Operators</title> + <tgroup cols="4"> + <thead> + <row> + <entry>Operator</entry> + <entry>Description</entry> + <entry>Example</entry> + <entry>Result</entry> + </row> + </thead> + <tbody> + <row> + <entry> <literal>=</literal> </entry> + <entry>equal</entry> + <entry><literal>int4range(1,5) = '[1,4]'::int4range</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal><></literal> </entry> + <entry>not equal</entry> + <entry><literal>numrange(1.1,2.2) <> numrange(1.1,2.3)</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal><</literal> </entry> + <entry>less than</entry> + <entry><literal>int4range(1,10) < int4range(2,3)</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal>></literal> </entry> + <entry>greater than</entry> + <entry><literal>int4range(1,10) > int4range(1,5)</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal><=</literal> </entry> + <entry>less than or equal</entry> + <entry><literal>numrange(1.1,2.2) <= numrange(1.1,2.2)</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal>>=</literal> </entry> + <entry>greater than or equal</entry> + <entry><literal>numrange(1.1,2.2) >= numrange(1.1,2.0)</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal>@></literal> </entry> + <entry>contains</entry> + <entry><literal>'[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal><@</literal> </entry> + <entry>is contained by</entry> + <entry><literal>int4range(2,4) <@ int4range(1,7)</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal>&&</literal> </entry> + <entry>overlap (have points in common)</entry> + <entry><literal>int8range(3,7) && int8range(4,12)</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal><<</literal> </entry> + <entry>strictly left of</entry> + <entry><literal>int8range(1,10) << int8range(100,110)</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal>>></literal> </entry> + <entry>strictly right of</entry> + <entry><literal>int8range(50,60) >> int8range(20,30)</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal>&<</literal> </entry> + <entry>Does not extend to the right of?</entry> + <entry><literal>int8range(1,20) &< int8range(18,20)</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal>&></literal> </entry> + <entry>Does not extend to the left of?</entry> + <entry><literal>int8range(7,20) &> int8range(5,10)</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal>-|-</literal> </entry> + <entry>adjacent?</entry> + <entry><literal>numrange(1.1,2.2) -|- numrange(2.2,3.3)</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal>+</literal> </entry> + <entry>Union</entry> + <entry><literal>numrange(5,15) + numrange(10,20)</literal></entry> + <entry><literal>[5,20)</literal></entry> + </row> + + <row> + <entry> <literal>-</literal> </entry> + <entry>Difference</entry> + <entry><literal>int8range(5,15) - int8range(10,20)</literal></entry> + <entry><literal>[5,10)</literal></entry> + </row> + + <row> + <entry> <literal>*</literal> </entry> + <entry>Intersection</entry> + <entry><literal>int8range(5,15) * int8range(10,20)</literal></entry> + <entry><literal>[10,15)</literal></entry> + </row> + + <row> + <entry> <literal>!?</literal> </entry> + <entry>Is empty?</entry> + <entry><literal>'empty'::int4range !?</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal>?</literal> </entry> + <entry>Is non-empty?</entry> + <entry><literal>numrange(1.0,2.0)?</literal></entry> + <entry><literal>t</literal></entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + Range comparisons compare the lower bounds first, and only if + equal, compare the upper bounds. This is generally most useful for + B-tree indexes, rather than being useful comparisons by themselves. + </para> + + <para> + See <xref linkend="rangetypes"> for more details about range operator + behavior. + </para> + + <para> + <xref linkend="range-functions-table"> shows the functions + available for use with range types. See <xref linkend="rangetypes"> + for more information and examples of the use of these functions. + </para> + + <indexterm> + <primary>lower</primary> + </indexterm> + <indexterm> + <primary>upper</primary> + </indexterm> + <indexterm> + <primary>empty</primary> + </indexterm> + <indexterm> + <primary>non_empty</primary> + </indexterm> + <indexterm> + <primary>lower_inc</primary> + </indexterm> + <indexterm> + <primary>upper_inc</primary> + </indexterm> + <indexterm> + <primary>lower_inf</primary> + </indexterm> + <indexterm> + <primary>upper_inf</primary> + </indexterm> + + <table id="range-functions-table"> + <title>Range Functions</title> + <tgroup cols="5"> + <thead> + <row> + <entry>Function</entry> + <entry>Return Type</entry> + <entry>Description</entry> + <entry>Example</entry> + <entry>Result</entry> + </row> + </thead> + <tbody> + <row> + <entry> + <literal> + <function>lower</function>(<type>anyrange</type>) + </literal> + </entry> + <entry><type>anyrange</type></entry> + <entry>lower bound of range</entry> + <entry><literal>lower(numrange(1.1,2.2))</literal></entry> + <entry><literal>1.1</literal></entry> + </row> + <row> + <entry> + <literal> + <function>upper</function>(<type>anyrange</type>) + </literal> + </entry> + <entry><type>anyrange</type></entry> + <entry>upper bound of range</entry> + <entry><literal>upper(numrange(1.1,2.2))</literal></entry> + <entry><literal>2.2</literal></entry> + </row> + <row> + <entry> + <literal> + <function>empty</function>(<type>anyrange</type>) + </literal> + </entry> + <entry><type>anyrange</type></entry> + <entry>is the range empty?</entry> + <entry><literal>empty(numrange(1.1,2.2))</literal></entry> + <entry><literal>false</literal></entry> + </row> + <row> + <entry> + <literal> + <function>non_empty</function>(<type>anyrange</type>) + </literal> + </entry> + <entry><type>anyrange</type></entry> + <entry>is the range non-empty?</entry> + <entry><literal>non_empty(numrange(1.1,2.2))</literal></entry> + <entry><literal>true</literal></entry> + </row> + <row> + <entry> + <literal> + <function>lower_inc</function>(<type>anyrange</type>) + </literal> + </entry> + <entry><type>anyrange</type></entry> + <entry>is the lower bound of the range inclusive?</entry> + <entry><literal>lower_inc(numrange(1.1,2.2))</literal></entry> + <entry><literal>true</literal></entry> + </row> + <row> + <entry> + <literal> + <function>upper_inc</function>(<type>anyrange</type>) + </literal> + </entry> + <entry><type>anyrange</type></entry> + <entry>is the upper bound of the range inclusive?</entry> + <entry><literal>upper_inc(numrange(1.1,2.2))</literal></entry> + <entry><literal>false</literal></entry> + </row> + <row> + <entry> + <literal> + <function>lower_inf</function>(<type>anyrange</type>) + </literal> + </entry> + <entry><type>anyrange</type></entry> + <entry>is the lower bound of the range infinite?</entry> + <entry><literal>lower_inf('(,)'::daterange)</literal></entry> + <entry><literal>true</literal></entry> + </row> + <row> + <entry> + <literal> + <function>upper_inf</function>(<type>anyrange</type>) + </literal> + </entry> + <entry><type>anyrange</type></entry> + <entry>is the upper bound of the range infinite?</entry> + <entry><literal>upper_inf('(,)'::daterange)</literal></entry> + <entry><literal>true</literal></entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + <sect1 id="functions-aggregate"> <title>Aggregate Functions</title> diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 19c15ad26fc..f33cef55ed0 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -139,7 +139,7 @@ <application>PL/pgSQL</> functions can also be declared to accept and return the polymorphic types <type>anyelement</type>, <type>anyarray</type>, <type>anynonarray</type>, - and <type>anyenum</>. The actual + <type>anyenum</>, and <type>anyrange</type>. The actual data types handled by a polymorphic function can vary from call to call, as discussed in <xref linkend="extend-types-polymorphic">. An example is shown in <xref linkend="plpgsql-declaration-parameters">. @@ -500,8 +500,8 @@ $$ LANGUAGE plpgsql; <para> When the return type of a <application>PL/pgSQL</application> function is declared as a polymorphic type (<type>anyelement</type>, - <type>anyarray</type>, <type>anynonarray</type>, or <type>anyenum</>), - a special parameter <literal>$0</literal> + <type>anyarray</type>, <type>anynonarray</type>, <type>anyenum</type>, + or <type>anyrange</type>), a special parameter <literal>$0</literal> is created. Its data type is the actual return type of the function, as deduced from the actual input types (see <xref linkend="extend-types-polymorphic">). diff --git a/doc/src/sgml/rangetypes.sgml b/doc/src/sgml/rangetypes.sgml new file mode 100644 index 00000000000..fc5896d8f42 --- /dev/null +++ b/doc/src/sgml/rangetypes.sgml @@ -0,0 +1,373 @@ +<!-- doc/src/sgml/rangetypes.sgml --> + +<sect1 id="rangetypes"> + <title>Range Types</title> + + <indexterm> + <primary>range type</primary> + </indexterm> + + <para> + Range types are data types representing a range of values over some + sub-type with a total order. For instance, ranges + of <type>timestamp</type> might be used to represent the ranges of + time that a meeting room is reserved. In this case the data type + is <type>tsrange</type> (short for "timestamp range"), + and <type>timestamp</type> is the sub-type with a total order. + </para> + + <para> + Range types are useful because they represent many points in a + single value. The use of time and date ranges for scheduling + purposes is the clearest example; but price ranges, measurement + ranges from an instrument, etc., are also useful. + </para> + + <sect2 id="rangetypes-builtin"> + <title>Built-in Range Types</title> + <para> + PostgreSQL comes with the following built-in range types: + <itemizedlist> + <listitem> + <para> + <type>INT4RANGE</type> -- Range of <type>INTEGER</type>. This is a discrete range type, see <xref linkend="rangetypes-discrete">. + </para> + </listitem> + <listitem> + <para> + <type>INT8RANGE</type> -- Range of <type>BIGINT</type>. This is a discrete range type, see <xref linkend="rangetypes-discrete">. + </para> + </listitem> + <listitem> + <para> + <type>NUMRANGE</type> -- Range of <type>NUMERIC</type>. + </para> + </listitem> + <listitem> + <para> + <type>TSRANGE</type> -- Range of <type>TIMESTAMP WITHOUT TIME ZONE</type>. + </para> + </listitem> + <listitem> + <para> + <type>TSTZRANGE</type> -- Range of <type>TIMESTAMP WITH TIME ZONE</type>. + </para> + </listitem> + <listitem> + <para> + <type>DATERANGE</type> -- Range of <type>DATE</type>. This is a discrete range type, see <xref linkend="rangetypes-discrete">. + </para> + </listitem> + </itemizedlist> + In addition, you can define your own; see <xref linkend="SQL-CREATETYPE"> for more information. + </para> + </sect2> + + <sect2 id="rangetypes-examples"> + <title>Examples</title> + <para> +<programlisting> +CREATE TABLE reservation ( during TSRANGE ); +INSERT INTO reservation VALUES + ( '[2010-01-01 14:30, 2010-01-01 15:30)' ); + +-- Containment +SELECT int4range(10, 20) @> 3; + +-- Overlaps +SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0); + +-- Find the upper bound: +SELECT upper(int8range(15, 25)); + +-- Compute the intersection: +SELECT int4range(10, 20) * int4range(15, 25); + +-- Is the range non-empty? +SELECT numrange(1, 5)? ; + +</programlisting> + + See <xref linkend="range-functions-table"> + and <xref linkend="range-operators-table"> for complete lists of + functions and operators on range types. + </para> + </sect2> + + <sect2 id="rangetypes-inclusivity"> + <title>Inclusive and Exclusive Bounds</title> + <para> + Every range has two bounds, the lower bound and the upper bound. All + points in between those values are included in the range. An + inclusive bound means that the boundary point itself is included in + the range as well, while an exclusive bound means that the boundary + point is not included in the range. + </para> + <para> + An inclusive lower bound is represented by <literal>[</literal> + while an exclusive lower bound is represented + by <literal>(</literal> (see <xref linkend="rangetypes-construct"> + and <xref linkend="rangetypes-io"> below). Likewise, an inclusive + upper bound is represented by <literal>]</literal>, while an + exclusive upper bound is represented by <literal>)</literal>. + </para> + <para> + Functions <literal>lower_inc</literal> + and <literal>upper_inc</literal> test the inclusivity of the lower + and upper bounds of a range, respectively. + </para> + </sect2> + + <sect2 id="rangetypes-infinite"> + <title>Infinite (unbounded) Ranges</title> + <para> + The lower bound of a range can be omitted, meaning that all points + less (or equal to, if inclusive) than the upper bound are included + in the range. Likewise, if the upper bound of the range is omitted, + then all points greater than (or equal to, if omitted) the lower + bound are included in the range. If both lower and upper bounds are + omitted, all points are considered to be in the range. + </para> + <para> + Functions <literal>lower_inf</literal> + and <literal>upper_inf</literal> test the range for infinite lower + and upper bounds of a range, respectively. + </para> + </sect2> + + <sect2 id="rangetypes-io"> + <title>Input/Output</title> + <para> + The input follows one of the following patterns: +<synopsis> +(<replaceable>lower-bound</replaceable>,<replaceable>upper-bound</replaceable>) +(<replaceable>lower-bound</replaceable>,<replaceable>upper-bound</replaceable>] +[<replaceable>lower-bound</replaceable>,<replaceable>upper-bound</replaceable>) +[<replaceable>lower-bound</replaceable>,<replaceable>upper-bound</replaceable>] +empty +</synopsis> + Notice that the final pattern is <literal>empty</literal>, which + represents an empty range (a range that contains no points). + </para> + <para> + The <replaceable>lower-bound</replaceable> may be either a string + that is valid input for the sub-type, or omitted (to indicate no + lower bound); and <replaceable>upper-bound</replaceable> may be + either a string that is valid input for the sub-type, or omitted (to + indicate no upper bound). + </para> + <para> + Either the <replaceable>lower-bound</replaceable> or + the <replaceable>upper-bound</replaceable> may be quoted + using <literal>""</literal> (double quotation marks), which will allow + special characters such as "<literal>,</literal>". Within quotation + marks, "<literal>\</literal>" (backslash) serves as an escape + character. + </para> + <para> + The choice between the other input formats affects the inclusivity + of the bounds. See <xref linkend="rangetypes-inclusivity">. + </para> + <para> + Examples: +<programlisting> +-- includes point 3, does not include point 7, and does include all points in between +select '[3,7)' + +-- does not include either 3 or 7, but includes all points in between +select '(3,7)' + +-- includes only the single point 4 +select '[4,4]' +</programlisting> + </para> + </sect2> + + <sect2 id="rangetypes-construct"> + <title>Constructing Ranges</title> + <para> + Each range type has a constructor by the same name. The constructor + accepts from zero to three arguments. The zero-argument form + constructs an empty range; the one-argument form constructs a + singleton range; the two-argument form constructs a range + in <literal>[ )</literal> form; and the three-argument form + constructs a range in a form specified by the third argument. For + example: +<programlisting> +-- Three-argument form: lower bound, upper bound, and third argument indicating +-- inclusivity/exclusivity of bounds (if omitted, defaults to <literal>'[)'</literal>). +SELECT numrange(1.0, 14.0, '(]'); + +-- The int4range input will exclude the lower bound and include the upper bound; but the +-- resulting output will appear in the canonical form; see <xref linkend="rangetypes-discrete">. +SELECT int8range(1, 14, '(]'); + +-- Single argument form constructs a singleton range; that is a range consisting of just +-- one point. +SELECT numrange(11.1); + +-- Zero-argument form constructs and empty range. +SELECT numrange(); + +-- Using NULL for a bound causes the range to be unbounded on that side; that is, negative +-- infinity for the lower bound or positive infinity for the upper bound. +SELECT numrange(NULL,2.2); +</programlisting> + </para> + </sect2> + + <sect2 id="rangetypes-discrete"> + <title>Discrete Range Types</title> + <para> + Discrete ranges are those that have a + defined <literal>canonical</literal> function. Loosely speaking, a + discrete range has a sub-type with a well-defined "step"; + e.g. <type>INTEGER</type> or <type>DATE</type>. + </para> + <para> + The <literal>canonical</literal> function should take an input range + value, and return an equal range value that may have a different + formatting. For instance, the integer range <literal>[1, + 7]</literal> could be represented by the equal integer + range <literal>[1, 8)</literal>. The two values are equal because + there are no points within the integer domain + between <literal>7</literal> and <literal>8</literal>, so not + including the end point <literal>8</literal> is the same as + including the end point <literal>7</literal>. The canonical output + for two values that are equal, like <literal>[1, 7]</literal> + and <literal>[1, 8)</literal>, must be equal. It doesn't matter + which representation you choose to be the canonical one, as long as + two equal values with different formattings are always mapped to the + same value with the same formatting. If the canonical function is + not specified, then ranges with different formatting + (e.g. <literal>[1, 7]</literal> and <literal>[1, 8)</literal>) will + always be treated as unequal. + </para> + <para> + For types such as <type>NUMRANGE</type>, this is not possible, + because there are always points in between two + distinct <type>NUMERIC</type> values. + </para> + <para> + The built-in range + types <type>INT4RANGE</type>, <type>INT8RANGE</type>, + and <type>DATERNAGE</type> all use a canonical form that includes + the lower bound and excludes the upper bound; that is, <literal>[ + )</literal>. User-defined ranges can use other conventions, however. + </para> + </sect2> + + <sect2 id="rangetypes-defining"> + <title>Defining New Range Types</title> + <para> + Users can define their own range types. The most common reason to do + this is to use ranges where the subtype is not among the built-in + range types, e.g. a range of type <type>FLOAT</type> (or, if the + subtype itself is a user-defined type). + </para> + <para> + For example: to define a new range type of sub-type <type>DOUBLE PRECISION</type>: +<programlisting> +CREATE TYPE FLOATRANGE AS RANGE ( + SUBTYPE = DOUBLE PRECISION +); + +SELECT '[1.234, 5.678]'::floatrange; +</programlisting> + Because <type>DOUBLE PRECISION</type> has no meaningful "step", we + do not define a <literal>canonical</literal> + function. See <xref linkend="SQL-CREATETYPE"> for more + information. + </para> + <para> + Defining your own range type also allows you to specify a different + operator class or collation to use (which affects the points that + fall between the range boundaries), or a different canonicalization + function. + </para> + </sect2> + + <sect2 id="rangetypes-gist"> + <indexterm> + <primary>range type</primary> + <secondary>gist</secondary> + </indexterm> + <title>Indexing</title> + <para> + GiST indexes can be applied to a table containing a range type. For instance: +<programlisting> +CREATE INDEX reservation_idx ON reservation USING gist (during); +</programlisting> + This index may speed up queries + involving <literal>&&</literal> + (overlaps), <literal>@></literal> (contains), and all the boolean + operators found in this + table: <xref linkend="range-operators-table">. + </para> + </sect2> + + <sect2 id="rangetypes-constraint"> + <indexterm> + <primary>range type</primary> + <secondary>exclude</secondary> + </indexterm> + <title>Constraints on Ranges</title> + <para> + While <literal>UNIQUE</literal> is a natural constraint for scalar + values, it is usually unsuitable for range types. Instead, an + exclusion constraint is often more appropriate + (see <link linkend="SQL-CREATETABLE-EXCLUDE">CREATE TABLE + ... CONSTRAINT ... EXCLUDE</link>). Exclusion constraints allow the + specification of constraints such as "non-overlapping" on a range + type. For example: +<programlisting> +ALTER TABLE reservation + ADD EXCLUDE USING gist (during WITH &&); +</programlisting> + That constraint will prevent any overlapping values from existing + in the table at the same time: +<programlisting> +INSERT INTO reservation VALUES + ( '[2010-01-01 11:30, 2010-01-01 13:00)' ); +-- Result: INSERT 0 1 +INSERT INTO reservation VALUES + ( '[2010-01-01 14:45, 2010-01-01 15:45)' ); +-- Result: +-- ERROR: conflicting key value violates exclusion constraint "reservation_during_excl" +-- DETAIL: Key (during)=([ 2010-01-01 14:45:00, 2010-01-01 15:45:00 )) conflicts with +-- existing key (during)=([ 2010-01-01 14:30:00, 2010-01-01 15:30:00 )). +</programlisting> + </para> + <para> + Combine range types and exclusion constraints + with <link linkend="btree-gist">btree_gist</link> for maximum + flexibility defining + constraints. After <literal>btree_gist</literal> is installed, the + following constraint will prevent overlapping ranges only if the + meeting room numbers are equal: +<programlisting> + +CREATE TABLE room_reservation +( + room TEXT, + during TSRANGE, + EXCLUDE USING gist (room WITH =, during WITH &&) +); + +INSERT INTO room_reservation VALUES + ( '123A', '[2010-01-01 14:00, 2010-01-01 15:00)' ); +-- Result: INSERT 0 1 +INSERT INTO room_reservation VALUES + ( '123A', '[2010-01-01 14:30, 2010-01-01 15:30)' ); +-- Result: +-- ERROR: conflicting key value violates exclusion constraint "room_reservation_room_during_excl" +-- DETAIL: Key (room, during)=(123A, [ 2010-01-01 14:30:00, 2010-01-01 15:30:00 )) conflicts with +-- existing key (room, during)=(123A, [ 2010-01-01 14:00:00, 2010-01-01 15:00:00 )). +INSERT INTO room_reservation VALUES + ( '123B', '[2010-01-01 14:30, 2010-01-01 15:30)' ); +-- Result: INSERT 0 1 + +</programlisting> + </para> + </sect2> +</sect1> diff --git a/doc/src/sgml/ref/create_type.sgml b/doc/src/sgml/ref/create_type.sgml index ea45fadae69..ebcd461bd91 100644 --- a/doc/src/sgml/ref/create_type.sgml +++ b/doc/src/sgml/ref/create_type.sgml @@ -27,6 +27,15 @@ CREATE TYPE <replaceable class="parameter">name</replaceable> AS CREATE TYPE <replaceable class="parameter">name</replaceable> AS ENUM ( [ '<replaceable class="parameter">label</replaceable>' [, ... ] ] ) +CREATE TYPE <replaceable class="parameter">name</replaceable> AS RANGE ( + SUBTYPE = <replaceable class="parameter">subtype</replaceable>, + [ , SUBTYPE_OPCLASS = <replaceable class="parameter">subtype_operator_class</replaceable> ] + [ , SUBTYPE_DIFF = <replaceable class="parameter">subtype_diff_function</replaceable> ] + [ , CANONICAL = <replaceable class="parameter">canonical_function</replaceable> ] + [ , ANALYZE = <replaceable class="parameter">analyze_function</replaceable> ] + [ , COLLATION = <replaceable class="parameter">collation</replaceable> ] +) + CREATE TYPE <replaceable class="parameter">name</replaceable> ( INPUT = <replaceable class="parameter">input_function</replaceable>, OUTPUT = <replaceable class="parameter">output_function</replaceable> @@ -98,11 +107,61 @@ CREATE TYPE <replaceable class="parameter">name</replaceable> </para> </refsect2> + <refsect2 id="SQL-CREATETYPE-RANGE"> + <title>Range Types</title> + + <para> + The third form of <command>CREATE TYPE</command> creates a new + range type, as described in <xref linkend="rangetypes">. + </para> + + <para> + The <replaceable class="parameter">subtype</replaceable> parameter + can be any type with an associated btree opclass (uses the type's + default btree operator class unless specified with + <replaceable class="parameter">subtype_operator_class</replaceable>). + </para> + + <para> + The <replaceable class="parameter">subtype_diff</replaceable> + function takes two values of type + <replaceable class="parameter">subtype</replaceable> as argument, and + returns the distance between the two values as + <type>double precision</type>. This function is used for GiST indexing + (see <xref linkend="gist"> for more information), and should be provided + for efficiency. + </para> + + <para> + The <replaceable class="parameter">canonical</replaceable> + function takes an argument and returns a value, both of the same + type being defined. This is used to convert the range value to a + canonical form, when applicable. See <xref linkend="rangetypes"> + for more information. To define + a <replaceable class="parameter">canonical</replaceable> function, + you must first create a <firstterm>shell type</>, which is a + placeholder type that has no properties except a name and an + owner. This is done by issuing the command <literal>CREATE TYPE + <replaceable>name</></literal>, with no additional parameters. + </para> + + <para> + The <replaceable class="parameter">analyze</replaceable> + function is the same as for creating a base type. + </para> + + <para> + The <replaceable class="parameter">collation</replaceable> option + specifies the collation used when determining the total order for + the range. + </para> + </refsect2> + <refsect2> <title>Base Types</title> <para> - The third form of <command>CREATE TYPE</command> creates a new base type + The fourth form of <command>CREATE TYPE</command> creates a new base type (scalar type). To create a new base type, you must be a superuser. (This restriction is made because an erroneous type definition could confuse or even crash the server.) diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 34e2cc29150..70643122046 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -997,8 +997,8 @@ $$ LANGUAGE SQL; <para> <acronym>SQL</acronym> functions can be declared to accept and return the polymorphic types <type>anyelement</type>, - <type>anyarray</type>, <type>anynonarray</type>, and - <type>anyenum</type>. See <xref + <type>anyarray</type>, <type>anynonarray</type>, + <type>anyenum</type>, and <type>anyrange</type>. See <xref linkend="extend-types-polymorphic"> for a more detailed explanation of polymorphic functions. Here is a polymorphic function <function>make_array</function> that builds up an array @@ -3046,7 +3046,7 @@ CREATE OR REPLACE FUNCTION retcomposite(IN integer, IN integer, C-language functions can be declared to accept and return the polymorphic types <type>anyelement</type>, <type>anyarray</type>, <type>anynonarray</type>, - and <type>anyenum</type>. + <type>anyenum</type>, and <type>anyrange</type>. See <xref linkend="extend-types-polymorphic"> for a more detailed explanation of polymorphic functions. When function arguments or return types are defined as polymorphic types, the function author cannot know |