<?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>9.13. Text Search Functions and Operators</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="functions-net.html" title="9.12. Network Address Functions and Operators" /><link rel="next" href="functions-xml.html" title="9.14. XML Functions" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.13. Text Search Functions and Operators</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-net.html" title="9.12. Network Address Functions and Operators">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-xml.html" title="9.14. XML Functions">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="FUNCTIONS-TEXTSEARCH"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.13. Text Search Functions and Operators</h2></div></div></div><a id="id-1.5.8.18.2" class="indexterm"></a><a id="id-1.5.8.18.3" class="indexterm"></a><p>
<a class="xref" href="functions-textsearch.html#TEXTSEARCH-OPERATORS-TABLE" title="Table 9.40. Text Search Operators">Table 9.40</a>,
<a class="xref" href="functions-textsearch.html#TEXTSEARCH-FUNCTIONS-TABLE" title="Table 9.41. Text Search Functions">Table 9.41</a> and
<a class="xref" href="functions-textsearch.html#TEXTSEARCH-FUNCTIONS-DEBUG-TABLE" title="Table 9.42. Text Search Debugging Functions">Table 9.42</a>
summarize the functions and operators that are provided
for full text searching. See <a class="xref" href="textsearch.html" title="Chapter 12. Full Text Search">Chapter 12</a> for a detailed
explanation of <span class="productname">PostgreSQL</span>'s text search
facility.
</p><div class="table" id="TEXTSEARCH-OPERATORS-TABLE"><p class="title"><strong>Table 9.40. Text Search Operators</strong></p><div class="table-contents"><table class="table" summary="Text Search Operators" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Operator</th><th>Return Type</th><th>Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td> <code class="literal">@@</code> </td><td><code class="type">boolean</code></td><td><code class="type">tsvector</code> matches <code class="type">tsquery</code> ?</td><td><code class="literal">to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat')</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal">@@@</code> </td><td><code class="type">boolean</code></td><td>deprecated synonym for <code class="literal">@@</code></td><td><code class="literal">to_tsvector('fat cats ate rats') @@@ to_tsquery('cat & rat')</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal">||</code> </td><td><code class="type">tsvector</code></td><td>concatenate <code class="type">tsvector</code>s</td><td><code class="literal">'a:1 b:2'::tsvector || 'c:1 d:2 b:3'::tsvector</code></td><td><code class="literal">'a':1 'b':2,5 'c':3 'd':4</code></td></tr><tr><td> <code class="literal">&&</code> </td><td><code class="type">tsquery</code></td><td>AND <code class="type">tsquery</code>s together</td><td><code class="literal">'fat | rat'::tsquery && 'cat'::tsquery</code></td><td><code class="literal">( 'fat' | 'rat' ) & 'cat'</code></td></tr><tr><td> <code class="literal">||</code> </td><td><code class="type">tsquery</code></td><td>OR <code class="type">tsquery</code>s together</td><td><code class="literal">'fat | rat'::tsquery || 'cat'::tsquery</code></td><td><code class="literal">( 'fat' | 'rat' ) | 'cat'</code></td></tr><tr><td> <code class="literal">!!</code> </td><td><code class="type">tsquery</code></td><td>negate a <code class="type">tsquery</code></td><td><code class="literal">!! 'cat'::tsquery</code></td><td><code class="literal">!'cat'</code></td></tr><tr><td> <code class="literal"><-></code> </td><td><code class="type">tsquery</code></td><td><code class="type">tsquery</code> followed by <code class="type">tsquery</code></td><td><code class="literal">to_tsquery('fat') <-> to_tsquery('rat')</code></td><td><code class="literal">'fat' <-> 'rat'</code></td></tr><tr><td> <code class="literal">@></code> </td><td><code class="type">boolean</code></td><td><code class="type">tsquery</code> contains another ?</td><td><code class="literal">'cat'::tsquery @> 'cat & rat'::tsquery</code></td><td><code class="literal">f</code></td></tr><tr><td> <code class="literal"><@</code> </td><td><code class="type">boolean</code></td><td><code class="type">tsquery</code> is contained in ?</td><td><code class="literal">'cat'::tsquery <@ 'cat & rat'::tsquery</code></td><td><code class="literal">t</code></td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>
The <code class="type">tsquery</code> containment operators consider only the lexemes
listed in the two queries, ignoring the combining operators.
</p></div><p>
In addition to the operators shown in the table, the ordinary B-tree
comparison operators (<code class="literal">=</code>, <code class="literal"><</code>, etc) are defined
for types <code class="type">tsvector</code> and <code class="type">tsquery</code>. These are not very
useful for text searching but allow, for example, unique indexes to be
built on columns of these types.
</p><div class="table" id="TEXTSEARCH-FUNCTIONS-TABLE"><p class="title"><strong>Table 9.41. Text Search Functions</strong></p><div class="table-contents"><table class="table" summary="Text Search Functions" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Return Type</th><th>Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td>
<a id="id-1.5.8.18.8.2.2.1.1.1" class="indexterm"></a>
<code class="literal"><code class="function">array_to_tsvector(<code class="type">text[]</code>)</code></code>
</td><td><code class="type">tsvector</code></td><td>convert array of lexemes to <code class="type">tsvector</code></td><td><code class="literal">array_to_tsvector('{fat,cat,rat}'::text[])</code></td><td><code class="literal">'cat' 'fat' 'rat'</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.2.1.1" class="indexterm"></a>
<code class="literal"><code class="function">get_current_ts_config()</code></code>
</td><td><code class="type">regconfig</code></td><td>get default text search configuration</td><td><code class="literal">get_current_ts_config()</code></td><td><code class="literal">english</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.3.1.1" class="indexterm"></a>
<code class="literal"><code class="function">length(<code class="type">tsvector</code>)</code></code>
</td><td><code class="type">integer</code></td><td>number of lexemes in <code class="type">tsvector</code></td><td><code class="literal">length('fat:2,4 cat:3 rat:5A'::tsvector)</code></td><td><code class="literal">3</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.4.1.1" class="indexterm"></a>
<code class="literal"><code class="function">numnode(<code class="type">tsquery</code>)</code></code>
</td><td><code class="type">integer</code></td><td>number of lexemes plus operators in <code class="type">tsquery</code></td><td><code class="literal"> numnode('(fat & rat) | cat'::tsquery)</code></td><td><code class="literal">5</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.5.1.1" class="indexterm"></a>
<code class="literal"><code class="function">plainto_tsquery([<span class="optional"> <em class="replaceable"><code>config</code></em> <code class="type">regconfig</code> , </span>] <em class="replaceable"><code>query</code></em> <code class="type">text</code>)</code></code>
</td><td><code class="type">tsquery</code></td><td>produce <code class="type">tsquery</code> ignoring punctuation</td><td><code class="literal">plainto_tsquery('english', 'The Fat Rats')</code></td><td><code class="literal">'fat' & 'rat'</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.6.1.1" class="indexterm"></a>
<code class="literal"><code class="function">phraseto_tsquery([<span class="optional"> <em class="replaceable"><code>config</code></em> <code class="type">regconfig</code> , </span>] <em class="replaceable"><code>query</code></em> <code class="type">text</code>)</code></code>
</td><td><code class="type">tsquery</code></td><td>produce <code class="type">tsquery</code> that searches for a phrase,
ignoring punctuation</td><td><code class="literal">phraseto_tsquery('english', 'The Fat Rats')</code></td><td><code class="literal">'fat' <-> 'rat'</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.7.1.1" class="indexterm"></a>
<code class="literal"><code class="function">websearch_to_tsquery([<span class="optional"> <em class="replaceable"><code>config</code></em> <code class="type">regconfig</code> , </span>] <em class="replaceable"><code>query</code></em> <code class="type">text</code>)</code></code>
</td><td><code class="type">tsquery</code></td><td>produce <code class="type">tsquery</code> from a web search style query</td><td><code class="literal">websearch_to_tsquery('english', '"fat rat" or rat')</code></td><td><code class="literal">'fat' <-> 'rat' | 'rat'</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.8.1.1" class="indexterm"></a>
<code class="literal"><code class="function">querytree(<em class="replaceable"><code>query</code></em> <code class="type">tsquery</code>)</code></code>
</td><td><code class="type">text</code></td><td>get indexable part of a <code class="type">tsquery</code></td><td><code class="literal">querytree('foo & ! bar'::tsquery)</code></td><td><code class="literal">'foo'</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.9.1.1" class="indexterm"></a>
<code class="literal"><code class="function">setweight(<em class="replaceable"><code>vector</code></em> <code class="type">tsvector</code>, <em class="replaceable"><code>weight</code></em> <code class="type">"char"</code>)</code></code>
</td><td><code class="type">tsvector</code></td><td>assign <em class="replaceable"><code>weight</code></em> to each element of <em class="replaceable"><code>vector</code></em></td><td><code class="literal">setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A')</code></td><td><code class="literal">'cat':3A 'fat':2A,4A 'rat':5A</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.10.1.1" class="indexterm"></a>
<code class="literal"><code class="function">setweight(<em class="replaceable"><code>vector</code></em> <code class="type">tsvector</code>, <em class="replaceable"><code>weight</code></em> <code class="type">"char"</code>, <em class="replaceable"><code>lexemes</code></em> <code class="type">text[]</code>)</code></code>
</td><td><code class="type">tsvector</code></td><td>assign <em class="replaceable"><code>weight</code></em> to elements of <em class="replaceable"><code>vector</code></em> that are listed in <em class="replaceable"><code>lexemes</code></em></td><td><code class="literal">setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A', '{cat,rat}')</code></td><td><code class="literal">'cat':3A 'fat':2,4 'rat':5A</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.11.1.1" class="indexterm"></a>
<code class="literal"><code class="function">strip(<code class="type">tsvector</code>)</code></code>
</td><td><code class="type">tsvector</code></td><td>remove positions and weights from <code class="type">tsvector</code></td><td><code class="literal">strip('fat:2,4 cat:3 rat:5A'::tsvector)</code></td><td><code class="literal">'cat' 'fat' 'rat'</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.12.1.1" class="indexterm"></a>
<code class="literal"><code class="function">to_tsquery([<span class="optional"> <em class="replaceable"><code>config</code></em> <code class="type">regconfig</code> , </span>] <em class="replaceable"><code>query</code></em> <code class="type">text</code>)</code></code>
</td><td><code class="type">tsquery</code></td><td>normalize words and convert to <code class="type">tsquery</code></td><td><code class="literal">to_tsquery('english', 'The & Fat & Rats')</code></td><td><code class="literal">'fat' & 'rat'</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.13.1.1" class="indexterm"></a>
<code class="literal"><code class="function">to_tsvector([<span class="optional"> <em class="replaceable"><code>config</code></em> <code class="type">regconfig</code> , </span>] <em class="replaceable"><code>document</code></em> <code class="type">text</code>)</code></code>
</td><td><code class="type">tsvector</code></td><td>reduce document text to <code class="type">tsvector</code></td><td><code class="literal">to_tsvector('english', 'The Fat Rats')</code></td><td><code class="literal">'fat':2 'rat':3</code></td></tr><tr><td>
<code class="literal"><code class="function">to_tsvector([<span class="optional"> <em class="replaceable"><code>config</code></em> <code class="type">regconfig</code> , </span>] <em class="replaceable"><code>document</code></em> <code class="type">json(b)</code>)</code></code>
</td><td><code class="type">tsvector</code></td><td>
reduce each string value in the document to a <code class="type">tsvector</code>, and then
concatenate those in document order to produce a single <code class="type">tsvector</code>
</td><td><code class="literal">to_tsvector('english', '{"a": "The Fat Rats"}'::json)</code></td><td><code class="literal">'fat':2 'rat':3</code></td></tr><tr><td>
<code class="literal"><code class="function">json(b)_to_tsvector([<span class="optional"> <em class="replaceable"><code>config</code></em> <code class="type">regconfig</code>,
</span>] <em class="replaceable"><code>document</code></em> <code class="type">json(b)</code>,
<em class="replaceable"><code>filter</code></em> <code class="type">json(b)</code>)</code></code>
</td><td><code class="type">tsvector</code></td><td>
reduce each value in the document, specified by <em class="replaceable"><code>filter</code></em> to a <code class="type">tsvector</code>,
and then concatenate those in document order to produce a single <code class="type">tsvector</code>.
<em class="replaceable"><code>filter</code></em> is a <code class="type">jsonb</code> array, that enumerates what kind of elements need to be included
into the resulting <code class="type">tsvector</code>. Possible values for <em class="replaceable"><code>filter</code></em> are
<code class="literal">"string"</code> (to include all string values), <code class="literal">"numeric"</code> (to include all numeric values in the string format),
<code class="literal">"boolean"</code> (to include all Boolean values in the string format <code class="literal">"true"</code>/<code class="literal">"false"</code>),
<code class="literal">"key"</code> (to include all keys) or <code class="literal">"all"</code> (to include all above). These values
can be combined together to include, e.g. all string and numeric values.
</td><td><code class="literal">json_to_tsvector('english', '{"a": "The Fat Rats", "b": 123}'::json, '["string", "numeric"]')</code></td><td><code class="literal">'123':5 'fat':2 'rat':3</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.16.1.1" class="indexterm"></a>
<code class="literal"><code class="function">ts_delete(<em class="replaceable"><code>vector</code></em> <code class="type">tsvector</code>, <em class="replaceable"><code>lexeme</code></em> <code class="type">text</code>)</code></code>
</td><td><code class="type">tsvector</code></td><td>remove given <em class="replaceable"><code>lexeme</code></em> from <em class="replaceable"><code>vector</code></em></td><td><code class="literal">ts_delete('fat:2,4 cat:3 rat:5A'::tsvector, 'fat')</code></td><td><code class="literal">'cat':3 'rat':5A</code></td></tr><tr><td>
<code class="literal"><code class="function">ts_delete(<em class="replaceable"><code>vector</code></em> <code class="type">tsvector</code>, <em class="replaceable"><code>lexemes</code></em> <code class="type">text[]</code>)</code></code>
</td><td><code class="type">tsvector</code></td><td>remove any occurrence of lexemes in <em class="replaceable"><code>lexemes</code></em> from <em class="replaceable"><code>vector</code></em></td><td><code class="literal">ts_delete('fat:2,4 cat:3 rat:5A'::tsvector, ARRAY['fat','rat'])</code></td><td><code class="literal">'cat':3</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.18.1.1" class="indexterm"></a>
<code class="literal"><code class="function">ts_filter(<em class="replaceable"><code>vector</code></em> <code class="type">tsvector</code>, <em class="replaceable"><code>weights</code></em> <code class="type">"char"[]</code>)</code></code>
</td><td><code class="type">tsvector</code></td><td>select only elements with given <em class="replaceable"><code>weights</code></em> from <em class="replaceable"><code>vector</code></em></td><td><code class="literal">ts_filter('fat:2,4 cat:3b rat:5A'::tsvector, '{a,b}')</code></td><td><code class="literal">'cat':3B 'rat':5A</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.19.1.1" class="indexterm"></a>
<code class="literal"><code class="function">ts_headline([<span class="optional"> <em class="replaceable"><code>config</code></em> <code class="type">regconfig</code>, </span>] <em class="replaceable"><code>document</code></em> <code class="type">text</code>, <em class="replaceable"><code>query</code></em> <code class="type">tsquery</code> [<span class="optional">, <em class="replaceable"><code>options</code></em> <code class="type">text</code> </span>])</code></code>
</td><td><code class="type">text</code></td><td>display a query match</td><td><code class="literal">ts_headline('x y z', 'z'::tsquery)</code></td><td><code class="literal">x y <b>z</b></code></td></tr><tr><td>
<code class="literal"><code class="function">ts_headline([<span class="optional"> <em class="replaceable"><code>config</code></em> <code class="type">regconfig</code>, </span>] <em class="replaceable"><code>document</code></em> <code class="type">json(b)</code>, <em class="replaceable"><code>query</code></em> <code class="type">tsquery</code> [<span class="optional">, <em class="replaceable"><code>options</code></em> <code class="type">text</code> </span>])</code></code>
</td><td><code class="type">text</code></td><td>display a query match</td><td><code class="literal">ts_headline('{"a":"x y z"}'::json, 'z'::tsquery)</code></td><td><code class="literal">{"a":"x y <b>z</b>"}</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.21.1.1" class="indexterm"></a>
<code class="literal"><code class="function">ts_rank([<span class="optional"> <em class="replaceable"><code>weights</code></em> <code class="type">float4[]</code>, </span>] <em class="replaceable"><code>vector</code></em> <code class="type">tsvector</code>, <em class="replaceable"><code>query</code></em> <code class="type">tsquery</code> [<span class="optional">, <em class="replaceable"><code>normalization</code></em> <code class="type">integer</code> </span>])</code></code>
</td><td><code class="type">float4</code></td><td>rank document for query</td><td><code class="literal">ts_rank(textsearch, query)</code></td><td><code class="literal">0.818</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.22.1.1" class="indexterm"></a>
<code class="literal"><code class="function">ts_rank_cd([<span class="optional"> <em class="replaceable"><code>weights</code></em> <code class="type">float4[]</code>, </span>] <em class="replaceable"><code>vector</code></em> <code class="type">tsvector</code>, <em class="replaceable"><code>query</code></em> <code class="type">tsquery</code> [<span class="optional">, <em class="replaceable"><code>normalization</code></em> <code class="type">integer</code> </span>])</code></code>
</td><td><code class="type">float4</code></td><td>rank document for query using cover density</td><td><code class="literal">ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', textsearch, query)</code></td><td><code class="literal">2.01317</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.23.1.1" class="indexterm"></a>
<code class="literal"><code class="function">ts_rewrite(<em class="replaceable"><code>query</code></em> <code class="type">tsquery</code>, <em class="replaceable"><code>target</code></em> <code class="type">tsquery</code>, <em class="replaceable"><code>substitute</code></em> <code class="type">tsquery</code>)</code></code>
</td><td><code class="type">tsquery</code></td><td>replace <em class="replaceable"><code>target</code></em> with <em class="replaceable"><code>substitute</code></em>
within query</td><td><code class="literal">ts_rewrite('a & b'::tsquery, 'a'::tsquery, 'foo|bar'::tsquery)</code></td><td><code class="literal">'b' & ( 'foo' | 'bar' )</code></td></tr><tr><td><code class="literal"><code class="function">ts_rewrite(<em class="replaceable"><code>query</code></em> <code class="type">tsquery</code>, <em class="replaceable"><code>select</code></em> <code class="type">text</code>)</code></code></td><td><code class="type">tsquery</code></td><td>replace using targets and substitutes from a <code class="command">SELECT</code> command</td><td><code class="literal">SELECT ts_rewrite('a & b'::tsquery, 'SELECT t,s FROM aliases')</code></td><td><code class="literal">'b' & ( 'foo' | 'bar' )</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.25.1.1" class="indexterm"></a>
<code class="literal"><code class="function">tsquery_phrase(<em class="replaceable"><code>query1</code></em> <code class="type">tsquery</code>, <em class="replaceable"><code>query2</code></em> <code class="type">tsquery</code>)</code></code>
</td><td><code class="type">tsquery</code></td><td>make query that searches for <em class="replaceable"><code>query1</code></em> followed
by <em class="replaceable"><code>query2</code></em> (same as <code class="literal"><-></code>
operator)</td><td><code class="literal">tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'))</code></td><td><code class="literal">'fat' <-> 'cat'</code></td></tr><tr><td>
<code class="literal"><code class="function">tsquery_phrase(<em class="replaceable"><code>query1</code></em> <code class="type">tsquery</code>, <em class="replaceable"><code>query2</code></em> <code class="type">tsquery</code>, <em class="replaceable"><code>distance</code></em> <code class="type">integer</code>)</code></code>
</td><td><code class="type">tsquery</code></td><td>make query that searches for <em class="replaceable"><code>query1</code></em> followed by
<em class="replaceable"><code>query2</code></em> at distance <em class="replaceable"><code>distance</code></em></td><td><code class="literal">tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'), 10)</code></td><td><code class="literal">'fat' <10> 'cat'</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.27.1.1" class="indexterm"></a>
<code class="literal"><code class="function">tsvector_to_array(<code class="type">tsvector</code>)</code></code>
</td><td><code class="type">text[]</code></td><td>convert <code class="type">tsvector</code> to array of lexemes</td><td><code class="literal">tsvector_to_array('fat:2,4 cat:3 rat:5A'::tsvector)</code></td><td><code class="literal">{cat,fat,rat}</code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.28.1.1" class="indexterm"></a>
<code class="literal"><code class="function">tsvector_update_trigger()</code></code>
</td><td><code class="type">trigger</code></td><td>trigger function for automatic <code class="type">tsvector</code> column update</td><td><code class="literal">CREATE TRIGGER ... tsvector_update_trigger(tsvcol, 'pg_catalog.swedish', title, body)</code></td><td><code class="literal"></code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.29.1.1" class="indexterm"></a>
<code class="literal"><code class="function">tsvector_update_trigger_column()</code></code>
</td><td><code class="type">trigger</code></td><td>trigger function for automatic <code class="type">tsvector</code> column update</td><td><code class="literal">CREATE TRIGGER ... tsvector_update_trigger_column(tsvcol, configcol, title, body)</code></td><td><code class="literal"></code></td></tr><tr><td>
<a id="id-1.5.8.18.8.2.2.30.1.1" class="indexterm"></a>
<code class="literal"><code class="function">unnest(<code class="type">tsvector</code>, OUT <em class="replaceable"><code>lexeme</code></em> <code class="type">text</code>, OUT <em class="replaceable"><code>positions</code></em> <code class="type">smallint[]</code>, OUT <em class="replaceable"><code>weights</code></em> <code class="type">text</code>)</code></code>
</td><td><code class="type">setof record</code></td><td>expand a <code class="type">tsvector</code> to a set of rows</td><td><code class="literal">unnest('fat:2,4 cat:3 rat:5A'::tsvector)</code></td><td><code class="literal">(cat,{3},{D}) ...</code></td></tr></tbody></table></div></div><br class="table-break" /><div class="note"><h3 class="title">Note</h3><p>
All the text search functions that accept an optional <code class="type">regconfig</code>
argument will use the configuration specified by
<a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TEXT-SEARCH-CONFIG">default_text_search_config</a>
when that argument is omitted.
</p></div><p>
The functions in
<a class="xref" href="functions-textsearch.html#TEXTSEARCH-FUNCTIONS-DEBUG-TABLE" title="Table 9.42. Text Search Debugging Functions">Table 9.42</a>
are listed separately because they are not usually used in everyday text
searching operations. They are helpful for development and debugging
of new text search configurations.
</p><div class="table" id="TEXTSEARCH-FUNCTIONS-DEBUG-TABLE"><p class="title"><strong>Table 9.42. Text Search Debugging Functions</strong></p><div class="table-contents"><table class="table" summary="Text Search Debugging Functions" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Return Type</th><th>Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td>
<a id="id-1.5.8.18.11.2.2.1.1.1" class="indexterm"></a>
<code class="literal"><code class="function">ts_debug([<span class="optional"> <em class="replaceable"><code>config</code></em> <code class="type">regconfig</code>, </span>] <em class="replaceable"><code>document</code></em> <code class="type">text</code>, OUT <em class="replaceable"><code>alias</code></em> <code class="type">text</code>, OUT <em class="replaceable"><code>description</code></em> <code class="type">text</code>, OUT <em class="replaceable"><code>token</code></em> <code class="type">text</code>, OUT <em class="replaceable"><code>dictionaries</code></em> <code class="type">regdictionary[]</code>, OUT <em class="replaceable"><code>dictionary</code></em> <code class="type">regdictionary</code>, OUT <em class="replaceable"><code>lexemes</code></em> <code class="type">text[]</code>)</code></code>
</td><td><code class="type">setof record</code></td><td>test a configuration</td><td><code class="literal">ts_debug('english', 'The Brightest supernovaes')</code></td><td><code class="literal">(asciiword,"Word, all ASCII",The,{english_stem},english_stem,{}) ...</code></td></tr><tr><td>
<a id="id-1.5.8.18.11.2.2.2.1.1" class="indexterm"></a>
<code class="literal"><code class="function">ts_lexize(<em class="replaceable"><code>dict</code></em> <code class="type">regdictionary</code>, <em class="replaceable"><code>token</code></em> <code class="type">text</code>)</code></code>
</td><td><code class="type">text[]</code></td><td>test a dictionary</td><td><code class="literal">ts_lexize('english_stem', 'stars')</code></td><td><code class="literal">{star}</code></td></tr><tr><td>
<a id="id-1.5.8.18.11.2.2.3.1.1" class="indexterm"></a>
<code class="literal"><code class="function">ts_parse(<em class="replaceable"><code>parser_name</code></em> <code class="type">text</code>, <em class="replaceable"><code>document</code></em> <code class="type">text</code>, OUT <em class="replaceable"><code>tokid</code></em> <code class="type">integer</code>, OUT <em class="replaceable"><code>token</code></em> <code class="type">text</code>)</code></code>
</td><td><code class="type">setof record</code></td><td>test a parser</td><td><code class="literal">ts_parse('default', 'foo - bar')</code></td><td><code class="literal">(1,foo) ...</code></td></tr><tr><td><code class="literal"><code class="function">ts_parse(<em class="replaceable"><code>parser_oid</code></em> <code class="type">oid</code>, <em class="replaceable"><code>document</code></em> <code class="type">text</code>, OUT <em class="replaceable"><code>tokid</code></em> <code class="type">integer</code>, OUT <em class="replaceable"><code>token</code></em> <code class="type">text</code>)</code></code></td><td><code class="type">setof record</code></td><td>test a parser</td><td><code class="literal">ts_parse(3722, 'foo - bar')</code></td><td><code class="literal">(1,foo) ...</code></td></tr><tr><td>
<a id="id-1.5.8.18.11.2.2.5.1.1" class="indexterm"></a>
<code class="literal"><code class="function">ts_token_type(<em class="replaceable"><code>parser_name</code></em> <code class="type">text</code>, OUT <em class="replaceable"><code>tokid</code></em> <code class="type">integer</code>, OUT <em class="replaceable"><code>alias</code></em> <code class="type">text</code>, OUT <em class="replaceable"><code>description</code></em> <code class="type">text</code>)</code></code>
</td><td><code class="type">setof record</code></td><td>get token types defined by parser</td><td><code class="literal">ts_token_type('default')</code></td><td><code class="literal">(1,asciiword,"Word, all ASCII") ...</code></td></tr><tr><td><code class="literal"><code class="function">ts_token_type(<em class="replaceable"><code>parser_oid</code></em> <code class="type">oid</code>, OUT <em class="replaceable"><code>tokid</code></em> <code class="type">integer</code>, OUT <em class="replaceable"><code>alias</code></em> <code class="type">text</code>, OUT <em class="replaceable"><code>description</code></em> <code class="type">text</code>)</code></code></td><td><code class="type">setof record</code></td><td>get token types defined by parser</td><td><code class="literal">ts_token_type(3722)</code></td><td><code class="literal">(1,asciiword,"Word, all ASCII") ...</code></td></tr><tr><td>
<a id="id-1.5.8.18.11.2.2.7.1.1" class="indexterm"></a>
<code class="literal"><code class="function">ts_stat(<em class="replaceable"><code>sqlquery</code></em> <code class="type">text</code>, [<span class="optional"> <em class="replaceable"><code>weights</code></em> <code class="type">text</code>, </span>] OUT <em class="replaceable"><code>word</code></em> <code class="type">text</code>, OUT <em class="replaceable"><code>ndoc</code></em> <code class="type">integer</code>, OUT <em class="replaceable"><code>nentry</code></em> <code class="type">integer</code>)</code></code>
</td><td><code class="type">setof record</code></td><td>get statistics of a <code class="type">tsvector</code> column</td><td><code class="literal">ts_stat('SELECT vector from apod')</code></td><td><code class="literal">(foo,10,15) ...</code></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-net.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-xml.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.12. Network Address Functions and Operators </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 9.14. XML Functions</td></tr></table></div></body></html>
