diff options
Diffstat (limited to 'doc/src/sgml/html/intarray.html')
| -rw-r--r-- | doc/src/sgml/html/intarray.html | 321 |
1 files changed, 321 insertions, 0 deletions
diff --git a/doc/src/sgml/html/intarray.html b/doc/src/sgml/html/intarray.html new file mode 100644 index 0000000..bbc4e1c --- /dev/null +++ b/doc/src/sgml/html/intarray.html @@ -0,0 +1,321 @@ +<?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>F.20. intarray</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="intagg.html" title="F.19. intagg" /><link rel="next" href="isn.html" title="F.21. isn" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.20. intarray</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="intagg.html" title="F.19. intagg">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules</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="isn.html" title="F.21. isn">Next</a></td></tr></table><hr /></div><div class="sect1" id="INTARRAY"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.20. intarray</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="intarray.html#id-1.11.7.29.7">F.20.1. <code class="filename">intarray</code> Functions and Operators</a></span></dt><dt><span class="sect2"><a href="intarray.html#id-1.11.7.29.8">F.20.2. Index Support</a></span></dt><dt><span class="sect2"><a href="intarray.html#id-1.11.7.29.9">F.20.3. Example</a></span></dt><dt><span class="sect2"><a href="intarray.html#id-1.11.7.29.10">F.20.4. Benchmark</a></span></dt><dt><span class="sect2"><a href="intarray.html#id-1.11.7.29.11">F.20.5. Authors</a></span></dt></dl></div><a id="id-1.11.7.29.2" class="indexterm"></a><p> + The <code class="filename">intarray</code> module provides a number of useful functions + and operators for manipulating null-free arrays of integers. + There is also support for indexed searches using some of the operators. + </p><p> + All of these operations will throw an error if a supplied array contains any + NULL elements. + </p><p> + Many of these operations are only sensible for one-dimensional arrays. + Although they will accept input arrays of more dimensions, the data is + treated as though it were a linear array in storage order. + </p><p> + This module is considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be + installed by non-superusers who have <code class="literal">CREATE</code> privilege + on the current database. + </p><div class="sect2" id="id-1.11.7.29.7"><div class="titlepage"><div><div><h3 class="title">F.20.1. <code class="filename">intarray</code> Functions and Operators</h3></div></div></div><p> + The functions provided by the <code class="filename">intarray</code> module + are shown in <a class="xref" href="intarray.html#INTARRAY-FUNC-TABLE" title="Table F.9. intarray Functions">Table F.9</a>, the operators + in <a class="xref" href="intarray.html#INTARRAY-OP-TABLE" title="Table F.10. intarray Operators">Table F.10</a>. + </p><div class="table" id="INTARRAY-FUNC-TABLE"><p class="title"><strong>Table F.9. <code class="filename">intarray</code> Functions</strong></p><div class="table-contents"><table class="table" summary="intarray Functions" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Function + </p> + <p> + Description + </p> + <p> + Example(s) + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.29.7.3.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">icount</code> ( <code class="type">integer[]</code> ) + → <code class="returnvalue">integer</code> + </p> + <p> + Returns the number of elements in the array. + </p> + <p> + <code class="literal">icount('{1,2,3}'::integer[])</code> + → <code class="returnvalue">3</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.29.7.3.2.2.2.1.1.1" class="indexterm"></a> + <code class="function">sort</code> ( <code class="type">integer[]</code>, <em class="parameter"><code>dir</code></em> <code class="type">text</code> ) + → <code class="returnvalue">integer[]</code> + </p> + <p> + Sorts the array in either ascending or descending order. + <em class="parameter"><code>dir</code></em> must be <code class="literal">asc</code> + or <code class="literal">desc</code>. + </p> + <p> + <code class="literal">sort('{1,3,2}'::integer[], 'desc')</code> + → <code class="returnvalue">{3,2,1}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">sort</code> ( <code class="type">integer[]</code> ) + → <code class="returnvalue">integer[]</code> + </p> + <p class="func_signature"> + <a id="id-1.11.7.29.7.3.2.2.3.1.2.1" class="indexterm"></a> + <code class="function">sort_asc</code> ( <code class="type">integer[]</code> ) + → <code class="returnvalue">integer[]</code> + </p> + <p> + Sorts in ascending order. + </p> + <p> + <code class="literal">sort(array[11,77,44])</code> + → <code class="returnvalue">{11,44,77}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.29.7.3.2.2.4.1.1.1" class="indexterm"></a> + <code class="function">sort_desc</code> ( <code class="type">integer[]</code> ) + → <code class="returnvalue">integer[]</code> + </p> + <p> + Sorts in descending order. + </p> + <p> + <code class="literal">sort_desc(array[11,77,44])</code> + → <code class="returnvalue">{77,44,11}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.29.7.3.2.2.5.1.1.1" class="indexterm"></a> + <code class="function">uniq</code> ( <code class="type">integer[]</code> ) + → <code class="returnvalue">integer[]</code> + </p> + <p> + Removes adjacent duplicates. + Often used with <code class="function">sort</code> to remove all duplicates. + </p> + <p> + <code class="literal">uniq('{1,2,2,3,1,1}'::integer[])</code> + → <code class="returnvalue">{1,2,3,1}</code> + </p> + <p> + <code class="literal">uniq(sort('{1,2,3,2,1}'::integer[]))</code> + → <code class="returnvalue">{1,2,3}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.29.7.3.2.2.6.1.1.1" class="indexterm"></a> + <code class="function">idx</code> ( <code class="type">integer[]</code>, <em class="parameter"><code>item</code></em> <code class="type">integer</code> ) + → <code class="returnvalue">integer</code> + </p> + <p> + Returns index of the first array element + matching <em class="parameter"><code>item</code></em>, or 0 if no match. + </p> + <p> + <code class="literal">idx(array[11,22,33,22,11], 22)</code> + → <code class="returnvalue">2</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.29.7.3.2.2.7.1.1.1" class="indexterm"></a> + <code class="function">subarray</code> ( <code class="type">integer[]</code>, <em class="parameter"><code>start</code></em> <code class="type">integer</code>, <em class="parameter"><code>len</code></em> <code class="type">integer</code> ) + → <code class="returnvalue">integer[]</code> + </p> + <p> + Extracts the portion of the array starting at + position <em class="parameter"><code>start</code></em>, with <em class="parameter"><code>len</code></em> + elements. + </p> + <p> + <code class="literal">subarray('{1,2,3,2,1}'::integer[], 2, 3)</code> + → <code class="returnvalue">{2,3,2}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">subarray</code> ( <code class="type">integer[]</code>, <em class="parameter"><code>start</code></em> <code class="type">integer</code> ) + → <code class="returnvalue">integer[]</code> + </p> + <p> + Extracts the portion of the array starting at + position <em class="parameter"><code>start</code></em>. + </p> + <p> + <code class="literal">subarray('{1,2,3,2,1}'::integer[], 2)</code> + → <code class="returnvalue">{2,3,2,1}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.11.7.29.7.3.2.2.9.1.1.1" class="indexterm"></a> + <code class="function">intset</code> ( <code class="type">integer</code> ) + → <code class="returnvalue">integer[]</code> + </p> + <p> + Makes a single-element array. + </p> + <p> + <code class="literal">intset(42)</code> + → <code class="returnvalue">{42}</code> + </p></td></tr></tbody></table></div></div><br class="table-break" /><div class="table" id="INTARRAY-OP-TABLE"><p class="title"><strong>Table F.10. <code class="filename">intarray</code> Operators</strong></p><div class="table-contents"><table class="table" summary="intarray Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Operator + </p> + <p> + Description + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">integer[]</code> <code class="literal">&&</code> <code class="type">integer[]</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Do arrays overlap (have at least one element in common)? + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">integer[]</code> <code class="literal">@></code> <code class="type">integer[]</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Does left array contain right array? + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">integer[]</code> <code class="literal"><@</code> <code class="type">integer[]</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Is left array contained in right array? + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type"></code> <code class="literal">#</code> <code class="type">integer[]</code> + → <code class="returnvalue">integer</code> + </p> + <p> + Returns the number of elements in the array. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">integer[]</code> <code class="literal">#</code> <code class="type">integer</code> + → <code class="returnvalue">integer</code> + </p> + <p> + Returns index of the first array element + matching the right argument, or 0 if no match. + (Same as <code class="function">idx</code> function.) + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">integer[]</code> <code class="literal">+</code> <code class="type">integer</code> + → <code class="returnvalue">integer[]</code> + </p> + <p> + Adds element to end of array. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">integer[]</code> <code class="literal">+</code> <code class="type">integer[]</code> + → <code class="returnvalue">integer[]</code> + </p> + <p> + Concatenates the arrays. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">integer[]</code> <code class="literal">-</code> <code class="type">integer</code> + → <code class="returnvalue">integer[]</code> + </p> + <p> + Removes entries matching the right argument from the array. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">integer[]</code> <code class="literal">-</code> <code class="type">integer[]</code> + → <code class="returnvalue">integer[]</code> + </p> + <p> + Removes elements of the right array from the left array. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">integer[]</code> <code class="literal">|</code> <code class="type">integer</code> + → <code class="returnvalue">integer[]</code> + </p> + <p> + Computes the union of the arguments. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">integer[]</code> <code class="literal">|</code> <code class="type">integer[]</code> + → <code class="returnvalue">integer[]</code> + </p> + <p> + Computes the union of the arguments. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">integer[]</code> <code class="literal">&</code> <code class="type">integer[]</code> + → <code class="returnvalue">integer[]</code> + </p> + <p> + Computes the intersection of the arguments. + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">integer[]</code> <code class="literal">@@</code> <code class="type">query_int</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Does array satisfy query? (see below) + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">query_int</code> <code class="literal">~~</code> <code class="type">integer[]</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Does array satisfy query? (commutator of <code class="literal">@@</code>) + </p></td></tr></tbody></table></div></div><br class="table-break" /><p> + The operators <code class="literal">&&</code>, <code class="literal">@></code> and + <code class="literal"><@</code> are equivalent to <span class="productname">PostgreSQL</span>'s built-in + operators of the same names, except that they work only on integer arrays + that do not contain nulls, while the built-in operators work for any array + type. This restriction makes them faster than the built-in operators + in many cases. + </p><p> + The <code class="literal">@@</code> and <code class="literal">~~</code> operators test whether an array + satisfies a <em class="firstterm">query</em>, which is expressed as a value of a + specialized data type <code class="type">query_int</code>. A <em class="firstterm">query</em> + consists of integer values that are checked against the elements of + the array, possibly combined using the operators <code class="literal">&</code> + (AND), <code class="literal">|</code> (OR), and <code class="literal">!</code> (NOT). Parentheses + can be used as needed. For example, + the query <code class="literal">1&(2|3)</code> matches arrays that contain 1 + and also contain either 2 or 3. + </p></div><div class="sect2" id="id-1.11.7.29.8"><div class="titlepage"><div><div><h3 class="title">F.20.2. Index Support</h3></div></div></div><p> + <code class="filename">intarray</code> provides index support for the + <code class="literal">&&</code>, <code class="literal">@></code>, + and <code class="literal">@@</code> operators, as well as regular array equality. + </p><p> + Two parameterized GiST index operator classes are provided: + <code class="literal">gist__int_ops</code> (used by default) is suitable for + small- to medium-size data sets, while + <code class="literal">gist__intbig_ops</code> uses a larger signature and is more + suitable for indexing large data sets (i.e., columns containing + a large number of distinct array values). + The implementation uses an RD-tree data structure with + built-in lossy compression. + </p><p> + <code class="literal">gist__int_ops</code> approximates an integer set as an array of + integer ranges. Its optional integer parameter <code class="literal">numranges</code> + determines the maximum number of ranges in + one index key. The default value of <code class="literal">numranges</code> is 100. + Valid values are between 1 and 253. Using larger arrays as GiST index + keys leads to a more precise search (scanning a smaller fraction of the index and + fewer heap pages), at the cost of a larger index. + </p><p> + <code class="literal">gist__intbig_ops</code> approximates an integer set as a bitmap + signature. Its optional integer parameter <code class="literal">siglen</code> + determines the signature length in bytes. + The default signature length is 16 bytes. Valid values of signature length + are between 1 and 2024 bytes. Longer signatures lead to a more precise + search (scanning a smaller fraction of the index and fewer heap pages), at + the cost of a larger index. + </p><p> + There is also a non-default GIN operator class + <code class="literal">gin__int_ops</code>, which supports these operators as well + as <code class="literal"><@</code>. + </p><p> + The choice between GiST and GIN indexing depends on the relative + performance characteristics of GiST and GIN, which are discussed elsewhere. + </p></div><div class="sect2" id="id-1.11.7.29.9"><div class="titlepage"><div><div><h3 class="title">F.20.3. Example</h3></div></div></div><pre class="programlisting"> +-- a message can be in one or more <span class="quote">“<span class="quote">sections</span>”</span> +CREATE TABLE message (mid INT PRIMARY KEY, sections INT[], ...); + +-- create specialized index with signature length of 32 bytes +CREATE INDEX message_rdtree_idx ON message USING GIST (sections gist__intbig_ops (siglen = 32)); + +-- select messages in section 1 OR 2 - OVERLAP operator +SELECT message.mid FROM message WHERE message.sections && '{1,2}'; + +-- select messages in sections 1 AND 2 - CONTAINS operator +SELECT message.mid FROM message WHERE message.sections @> '{1,2}'; + +-- the same, using QUERY operator +SELECT message.mid FROM message WHERE message.sections @@ '1&2'::query_int; +</pre></div><div class="sect2" id="id-1.11.7.29.10"><div class="titlepage"><div><div><h3 class="title">F.20.4. Benchmark</h3></div></div></div><p> + The source directory <code class="filename">contrib/intarray/bench</code> contains a + benchmark test suite, which can be run against an installed + <span class="productname">PostgreSQL</span> server. (It also requires <code class="filename">DBD::Pg</code> + to be installed.) To run: + </p><pre class="programlisting"> +cd .../contrib/intarray/bench +createdb TEST +psql -c "CREATE EXTENSION intarray" TEST +./create_test.pl | psql TEST +./bench.pl +</pre><p> + The <code class="filename">bench.pl</code> script has numerous options, which + are displayed when it is run without any arguments. + </p></div><div class="sect2" id="id-1.11.7.29.11"><div class="titlepage"><div><div><h3 class="title">F.20.5. Authors</h3></div></div></div><p> + All work was done by Teodor Sigaev (<code class="email"><<a class="email" href="mailto:teodor@sigaev.ru">teodor@sigaev.ru</a>></code>) and + Oleg Bartunov (<code class="email"><<a class="email" href="mailto:oleg@sai.msu.su">oleg@sai.msu.su</a>></code>). See + <a class="ulink" href="http://www.sai.msu.su/~megera/postgres/gist/" target="_top">http://www.sai.msu.su/~megera/postgres/gist/</a> for + additional information. Andrey Oktyabrski did a great work on adding new + functions and operations. + </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="intagg.html" title="F.19. intagg">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="isn.html" title="F.21. isn">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.19. intagg </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"> F.21. isn</td></tr></table></div></body></html>
\ No newline at end of file |