diff options
Diffstat (limited to 'doc/src/sgml/html/functions-textsearch.html')
| -rw-r--r-- | doc/src/sgml/html/functions-textsearch.html | 763 |
1 files changed, 763 insertions, 0 deletions
diff --git a/doc/src/sgml/html/functions-textsearch.html b/doc/src/sgml/html/functions-textsearch.html new file mode 100644 index 0000000..90f1290 --- /dev/null +++ b/doc/src/sgml/html/functions-textsearch.html @@ -0,0 +1,763 @@ +<?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-uuid.html" title="9.14. UUID Functions" /></head><body id="docContent" class="container-fluid col-10"><div 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 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-uuid.html" title="9.14. UUID Functions">Next</a></td></tr></table><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.19.2" class="indexterm"></a><a id="id-1.5.8.19.3" class="indexterm"></a><p> + <a class="xref" href="functions-textsearch.html#TEXTSEARCH-OPERATORS-TABLE" title="Table 9.42. Text Search Operators">Table 9.42</a>, + <a class="xref" href="functions-textsearch.html#TEXTSEARCH-FUNCTIONS-TABLE" title="Table 9.43. Text Search Functions">Table 9.43</a> and + <a class="xref" href="functions-textsearch.html#TEXTSEARCH-FUNCTIONS-DEBUG-TABLE" title="Table 9.44. Text Search Debugging Functions">Table 9.44</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.42. Text Search Operators</strong></p><div class="table-contents"><table class="table" summary="Text Search Operators" border="1"><colgroup><col /></colgroup><thead><tr><th class="func_table_entry"><p class="func_signature"> + Operator + </p> + <p> + Description + </p> + <p> + Example(s) + </p></th></tr></thead><tbody><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">tsvector</code> <code class="literal">@@</code> <code class="type">tsquery</code> + → <code class="returnvalue">boolean</code> + </p> + <p class="func_signature"> + <code class="type">tsquery</code> <code class="literal">@@</code> <code class="type">tsvector</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Does <code class="type">tsvector</code> match <code class="type">tsquery</code>? + (The arguments can be given in either order.) + </p> + <p> + <code class="literal">to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat')</code> + → <code class="returnvalue">t</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">text</code> <code class="literal">@@</code> <code class="type">tsquery</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Does text string, after implicit invocation + of <code class="function">to_tsvector()</code>, match <code class="type">tsquery</code>? + </p> + <p> + <code class="literal">'fat cats ate rats' @@ to_tsquery('cat & rat')</code> + → <code class="returnvalue">t</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">tsvector</code> <code class="literal">@@@</code> <code class="type">tsquery</code> + → <code class="returnvalue">boolean</code> + </p> + <p class="func_signature"> + <code class="type">tsquery</code> <code class="literal">@@@</code> <code class="type">tsvector</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + This is a deprecated synonym for <code class="literal">@@</code>. + </p> + <p> + <code class="literal">to_tsvector('fat cats ate rats') @@@ to_tsquery('cat & rat')</code> + → <code class="returnvalue">t</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">tsvector</code> <code class="literal">||</code> <code class="type">tsvector</code> + → <code class="returnvalue">tsvector</code> + </p> + <p> + Concatenates two <code class="type">tsvector</code>s. If both inputs contain + lexeme positions, the second input's positions are adjusted + accordingly. + </p> + <p> + <code class="literal">'a:1 b:2'::tsvector || 'c:1 d:2 b:3'::tsvector</code> + → <code class="returnvalue">'a':1 'b':2,5 'c':3 'd':4</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">tsquery</code> <code class="literal">&&</code> <code class="type">tsquery</code> + → <code class="returnvalue">tsquery</code> + </p> + <p> + ANDs two <code class="type">tsquery</code>s together, producing a query that + matches documents that match both input queries. + </p> + <p> + <code class="literal">'fat | rat'::tsquery && 'cat'::tsquery</code> + → <code class="returnvalue">( 'fat' | 'rat' ) & 'cat'</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">tsquery</code> <code class="literal">||</code> <code class="type">tsquery</code> + → <code class="returnvalue">tsquery</code> + </p> + <p> + ORs two <code class="type">tsquery</code>s together, producing a query that + matches documents that match either input query. + </p> + <p> + <code class="literal">'fat | rat'::tsquery || 'cat'::tsquery</code> + → <code class="returnvalue">'fat' | 'rat' | 'cat'</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="literal">!!</code> <code class="type">tsquery</code> + → <code class="returnvalue">tsquery</code> + </p> + <p> + Negates a <code class="type">tsquery</code>, producing a query that matches + documents that do not match the input query. + </p> + <p> + <code class="literal">!! 'cat'::tsquery</code> + → <code class="returnvalue">!'cat'</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">tsquery</code> <code class="literal"><-></code> <code class="type">tsquery</code> + → <code class="returnvalue">tsquery</code> + </p> + <p> + Constructs a phrase query, which matches if the two input queries + match at successive lexemes. + </p> + <p> + <code class="literal">to_tsquery('fat') <-> to_tsquery('rat')</code> + → <code class="returnvalue">'fat' <-> 'rat'</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">tsquery</code> <code class="literal">@></code> <code class="type">tsquery</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Does first <code class="type">tsquery</code> contain the second? (This considers + only whether all the lexemes appearing in one query appear in the + other, ignoring the combining operators.) + </p> + <p> + <code class="literal">'cat'::tsquery @> 'cat & rat'::tsquery</code> + → <code class="returnvalue">f</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="type">tsquery</code> <code class="literal"><@</code> <code class="type">tsquery</code> + → <code class="returnvalue">boolean</code> + </p> + <p> + Is first <code class="type">tsquery</code> contained in the second? (This + considers only whether all the lexemes appearing in one query appear + in the other, ignoring the combining operators.) + </p> + <p> + <code class="literal">'cat'::tsquery <@ 'cat & rat'::tsquery</code> + → <code class="returnvalue">t</code> + </p> + <p> + <code class="literal">'cat'::tsquery <@ '!cat & rat'::tsquery</code> + → <code class="returnvalue">t</code> + </p></td></tr></tbody></table></div></div><br class="table-break" /><p> + In addition to these specialized operators, the usual comparison + operators shown in <a class="xref" href="functions-comparison.html#FUNCTIONS-COMPARISON-OP-TABLE" title="Table 9.1. Comparison Operators">Table 9.1</a> are + available 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.43. Text Search Functions</strong></p><div class="table-contents"><table class="table" summary="Text Search 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.5.8.19.7.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">array_to_tsvector</code> ( <code class="type">text[]</code> ) + → <code class="returnvalue">tsvector</code> + </p> + <p> + Converts an array of text strings to a <code class="type">tsvector</code>. + The given strings are used as lexemes as-is, without further + processing. Array elements must not be empty strings + or <code class="literal">NULL</code>. + </p> + <p> + <code class="literal">array_to_tsvector('{fat,cat,rat}'::text[])</code> + → <code class="returnvalue">'cat' 'fat' 'rat'</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.2.1.1.1" class="indexterm"></a> + <code class="function">get_current_ts_config</code> ( ) + → <code class="returnvalue">regconfig</code> + </p> + <p> + Returns the OID of the current default text search configuration + (as set by <a class="xref" href="runtime-config-client.html#GUC-DEFAULT-TEXT-SEARCH-CONFIG">default_text_search_config</a>). + </p> + <p> + <code class="literal">get_current_ts_config()</code> + → <code class="returnvalue">english</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.3.1.1.1" class="indexterm"></a> + <code class="function">length</code> ( <code class="type">tsvector</code> ) + → <code class="returnvalue">integer</code> + </p> + <p> + Returns the number of lexemes in the <code class="type">tsvector</code>. + </p> + <p> + <code class="literal">length('fat:2,4 cat:3 rat:5A'::tsvector)</code> + → <code class="returnvalue">3</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.4.1.1.1" class="indexterm"></a> + <code class="function">numnode</code> ( <code class="type">tsquery</code> ) + → <code class="returnvalue">integer</code> + </p> + <p> + Returns the number of lexemes plus operators in + the <code class="type">tsquery</code>. + </p> + <p> + <code class="literal">numnode('(fat & rat) | cat'::tsquery)</code> + → <code class="returnvalue">5</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.5.1.1.1" class="indexterm"></a> + <code class="function">plainto_tsquery</code> ( + [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>] + <em class="parameter"><code>query</code></em> <code class="type">text</code> ) + → <code class="returnvalue">tsquery</code> + </p> + <p> + Converts text to a <code class="type">tsquery</code>, normalizing words according to + the specified or default configuration. Any punctuation in the string + is ignored (it does not determine query operators). The resulting + query matches documents containing all non-stopwords in the text. + </p> + <p> + <code class="literal">plainto_tsquery('english', 'The Fat Rats')</code> + → <code class="returnvalue">'fat' & 'rat'</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.6.1.1.1" class="indexterm"></a> + <code class="function">phraseto_tsquery</code> ( + [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>] + <em class="parameter"><code>query</code></em> <code class="type">text</code> ) + → <code class="returnvalue">tsquery</code> + </p> + <p> + Converts text to a <code class="type">tsquery</code>, normalizing words according to + the specified or default configuration. Any punctuation in the string + is ignored (it does not determine query operators). The resulting + query matches phrases containing all non-stopwords in the text. + </p> + <p> + <code class="literal">phraseto_tsquery('english', 'The Fat Rats')</code> + → <code class="returnvalue">'fat' <-> 'rat'</code> + </p> + <p> + <code class="literal">phraseto_tsquery('english', 'The Cat and Rats')</code> + → <code class="returnvalue">'cat' <2> 'rat'</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.7.1.1.1" class="indexterm"></a> + <code class="function">websearch_to_tsquery</code> ( + [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>] + <em class="parameter"><code>query</code></em> <code class="type">text</code> ) + → <code class="returnvalue">tsquery</code> + </p> + <p> + Converts text to a <code class="type">tsquery</code>, normalizing words according + to the specified or default configuration. Quoted word sequences are + converted to phrase tests. The word <span class="quote">“<span class="quote">or</span>”</span> is understood + as producing an OR operator, and a dash produces a NOT operator; + other punctuation is ignored. + This approximates the behavior of some common web search tools. + </p> + <p> + <code class="literal">websearch_to_tsquery('english', '"fat rat" or cat dog')</code> + → <code class="returnvalue">'fat' <-> 'rat' | 'cat' & 'dog'</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.8.1.1.1" class="indexterm"></a> + <code class="function">querytree</code> ( <code class="type">tsquery</code> ) + → <code class="returnvalue">text</code> + </p> + <p> + Produces a representation of the indexable portion of + a <code class="type">tsquery</code>. A result that is empty or + just <code class="literal">T</code> indicates a non-indexable query. + </p> + <p> + <code class="literal">querytree('foo & ! bar'::tsquery)</code> + → <code class="returnvalue">'foo'</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.9.1.1.1" class="indexterm"></a> + <code class="function">setweight</code> ( <em class="parameter"><code>vector</code></em> <code class="type">tsvector</code>, <em class="parameter"><code>weight</code></em> <code class="type">"char"</code> ) + → <code class="returnvalue">tsvector</code> + </p> + <p> + Assigns the specified <em class="parameter"><code>weight</code></em> to each element + of the <em class="parameter"><code>vector</code></em>. + </p> + <p> + <code class="literal">setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A')</code> + → <code class="returnvalue">'cat':3A 'fat':2A,4A 'rat':5A</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.10.1.1.1" class="indexterm"></a> + <code class="function">setweight</code> ( <em class="parameter"><code>vector</code></em> <code class="type">tsvector</code>, <em class="parameter"><code>weight</code></em> <code class="type">"char"</code>, <em class="parameter"><code>lexemes</code></em> <code class="type">text[]</code> ) + → <code class="returnvalue">tsvector</code> + </p> + <p> + Assigns the specified <em class="parameter"><code>weight</code></em> to elements + of the <em class="parameter"><code>vector</code></em> that are listed + in <em class="parameter"><code>lexemes</code></em>. + The strings in <em class="parameter"><code>lexemes</code></em> are taken as lexemes + as-is, without further processing. Strings that do not match any + lexeme in <em class="parameter"><code>vector</code></em> are ignored. + </p> + <p> + <code class="literal">setweight('fat:2,4 cat:3 rat:5,6B'::tsvector, 'A', '{cat,rat}')</code> + → <code class="returnvalue">'cat':3A 'fat':2,4 'rat':5A,6A</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.11.1.1.1" class="indexterm"></a> + <code class="function">strip</code> ( <code class="type">tsvector</code> ) + → <code class="returnvalue">tsvector</code> + </p> + <p> + Removes positions and weights from the <code class="type">tsvector</code>. + </p> + <p> + <code class="literal">strip('fat:2,4 cat:3 rat:5A'::tsvector)</code> + → <code class="returnvalue">'cat' 'fat' 'rat'</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.12.1.1.1" class="indexterm"></a> + <code class="function">to_tsquery</code> ( + [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>] + <em class="parameter"><code>query</code></em> <code class="type">text</code> ) + → <code class="returnvalue">tsquery</code> + </p> + <p> + Converts text to a <code class="type">tsquery</code>, normalizing words according to + the specified or default configuration. The words must be combined + by valid <code class="type">tsquery</code> operators. + </p> + <p> + <code class="literal">to_tsquery('english', 'The & Fat & Rats')</code> + → <code class="returnvalue">'fat' & 'rat'</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.13.1.1.1" class="indexterm"></a> + <code class="function">to_tsvector</code> ( + [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>] + <em class="parameter"><code>document</code></em> <code class="type">text</code> ) + → <code class="returnvalue">tsvector</code> + </p> + <p> + Converts text to a <code class="type">tsvector</code>, normalizing words according + to the specified or default configuration. Position information is + included in the result. + </p> + <p> + <code class="literal">to_tsvector('english', 'The Fat Rats')</code> + → <code class="returnvalue">'fat':2 'rat':3</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">to_tsvector</code> ( + [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>] + <em class="parameter"><code>document</code></em> <code class="type">json</code> ) + → <code class="returnvalue">tsvector</code> + </p> + <p class="func_signature"> + <code class="function">to_tsvector</code> ( + [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>] + <em class="parameter"><code>document</code></em> <code class="type">jsonb</code> ) + → <code class="returnvalue">tsvector</code> + </p> + <p> + Converts each string value in the JSON document to + a <code class="type">tsvector</code>, normalizing words according to the specified + or default configuration. The results are then concatenated in + document order to produce the output. Position information is + generated as though one stopword exists between each pair of string + values. (Beware that <span class="quote">“<span class="quote">document order</span>”</span> of the fields of a + JSON object is implementation-dependent when the input + is <code class="type">jsonb</code>; observe the difference in the examples.) + </p> + <p> + <code class="literal">to_tsvector('english', '{"aa": "The Fat Rats", "b": "dog"}'::json)</code> + → <code class="returnvalue">'dog':5 'fat':2 'rat':3</code> + </p> + <p> + <code class="literal">to_tsvector('english', '{"aa": "The Fat Rats", "b": "dog"}'::jsonb)</code> + → <code class="returnvalue">'dog':1 'fat':4 'rat':5</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.15.1.1.1" class="indexterm"></a> + <code class="function">json_to_tsvector</code> ( + [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>] + <em class="parameter"><code>document</code></em> <code class="type">json</code>, + <em class="parameter"><code>filter</code></em> <code class="type">jsonb</code> ) + → <code class="returnvalue">tsvector</code> + </p> + <p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.15.1.2.1" class="indexterm"></a> + <code class="function">jsonb_to_tsvector</code> ( + [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>] + <em class="parameter"><code>document</code></em> <code class="type">jsonb</code>, + <em class="parameter"><code>filter</code></em> <code class="type">jsonb</code> ) + → <code class="returnvalue">tsvector</code> + </p> + <p> + Selects each item in the JSON document that is requested by + the <em class="parameter"><code>filter</code></em> and converts each one to + a <code class="type">tsvector</code>, normalizing words according to the specified + or default configuration. The results are then concatenated in + document order to produce the output. Position information is + generated as though one stopword exists between each pair of selected + items. (Beware that <span class="quote">“<span class="quote">document order</span>”</span> of the fields of a + JSON object is implementation-dependent when the input + is <code class="type">jsonb</code>.) + The <em class="parameter"><code>filter</code></em> must be a <code class="type">jsonb</code> + array containing zero or more of these keywords: + <code class="literal">"string"</code> (to include all string values), + <code class="literal">"numeric"</code> (to include all numeric values), + <code class="literal">"boolean"</code> (to include all boolean values), + <code class="literal">"key"</code> (to include all keys), or + <code class="literal">"all"</code> (to include all the above). + As a special case, the <em class="parameter"><code>filter</code></em> can also be a + simple JSON value that is one of these keywords. + </p> + <p> + <code class="literal">json_to_tsvector('english', '{"a": "The Fat Rats", "b": 123}'::json, '["string", "numeric"]')</code> + → <code class="returnvalue">'123':5 'fat':2 'rat':3</code> + </p> + <p> + <code class="literal">json_to_tsvector('english', '{"cat": "The Fat Rats", "dog": 123}'::json, '"all"')</code> + → <code class="returnvalue">'123':9 'cat':1 'dog':7 'fat':4 'rat':5</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.16.1.1.1" class="indexterm"></a> + <code class="function">ts_delete</code> ( <em class="parameter"><code>vector</code></em> <code class="type">tsvector</code>, <em class="parameter"><code>lexeme</code></em> <code class="type">text</code> ) + → <code class="returnvalue">tsvector</code> + </p> + <p> + Removes any occurrence of the given <em class="parameter"><code>lexeme</code></em> + from the <em class="parameter"><code>vector</code></em>. + The <em class="parameter"><code>lexeme</code></em> string is treated as a lexeme as-is, + without further processing. + </p> + <p> + <code class="literal">ts_delete('fat:2,4 cat:3 rat:5A'::tsvector, 'fat')</code> + → <code class="returnvalue">'cat':3 'rat':5A</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">ts_delete</code> ( <em class="parameter"><code>vector</code></em> <code class="type">tsvector</code>, <em class="parameter"><code>lexemes</code></em> <code class="type">text[]</code> ) + → <code class="returnvalue">tsvector</code> + </p> + <p> + Removes any occurrences of the lexemes + in <em class="parameter"><code>lexemes</code></em> + from the <em class="parameter"><code>vector</code></em>. + The strings in <em class="parameter"><code>lexemes</code></em> are taken as lexemes + as-is, without further processing. Strings that do not match any + lexeme in <em class="parameter"><code>vector</code></em> are ignored. + </p> + <p> + <code class="literal">ts_delete('fat:2,4 cat:3 rat:5A'::tsvector, ARRAY['fat','rat'])</code> + → <code class="returnvalue">'cat':3</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.18.1.1.1" class="indexterm"></a> + <code class="function">ts_filter</code> ( <em class="parameter"><code>vector</code></em> <code class="type">tsvector</code>, <em class="parameter"><code>weights</code></em> <code class="type">"char"[]</code> ) + → <code class="returnvalue">tsvector</code> + </p> + <p> + Selects only elements with the given <em class="parameter"><code>weights</code></em> + from the <em class="parameter"><code>vector</code></em>. + </p> + <p> + <code class="literal">ts_filter('fat:2,4 cat:3b,7c rat:5A'::tsvector, '{a,b}')</code> + → <code class="returnvalue">'cat':3B 'rat':5A</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.19.1.1.1" class="indexterm"></a> + <code class="function">ts_headline</code> ( + [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>] + <em class="parameter"><code>document</code></em> <code class="type">text</code>, + <em class="parameter"><code>query</code></em> <code class="type">tsquery</code> + [<span class="optional">, <em class="parameter"><code>options</code></em> <code class="type">text</code> </span>] ) + → <code class="returnvalue">text</code> + </p> + <p> + Displays, in an abbreviated form, the match(es) for + the <em class="parameter"><code>query</code></em> in + the <em class="parameter"><code>document</code></em>, which must be raw text not + a <code class="type">tsvector</code>. Words in the document are normalized + according to the specified or default configuration before matching to + the query. Use of this function is discussed in + <a class="xref" href="textsearch-controls.html#TEXTSEARCH-HEADLINE" title="12.3.4. Highlighting Results">Section 12.3.4</a>, which also describes the + available <em class="parameter"><code>options</code></em>. + </p> + <p> + <code class="literal">ts_headline('The fat cat ate the rat.', 'cat')</code> + → <code class="returnvalue">The fat <b>cat</b> ate the rat.</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">ts_headline</code> ( + [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>] + <em class="parameter"><code>document</code></em> <code class="type">json</code>, + <em class="parameter"><code>query</code></em> <code class="type">tsquery</code> + [<span class="optional">, <em class="parameter"><code>options</code></em> <code class="type">text</code> </span>] ) + → <code class="returnvalue">text</code> + </p> + <p class="func_signature"> + <code class="function">ts_headline</code> ( + [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>] + <em class="parameter"><code>document</code></em> <code class="type">jsonb</code>, + <em class="parameter"><code>query</code></em> <code class="type">tsquery</code> + [<span class="optional">, <em class="parameter"><code>options</code></em> <code class="type">text</code> </span>] ) + → <code class="returnvalue">text</code> + </p> + <p> + Displays, in an abbreviated form, match(es) for + the <em class="parameter"><code>query</code></em> that occur in string values + within the JSON <em class="parameter"><code>document</code></em>. + See <a class="xref" href="textsearch-controls.html#TEXTSEARCH-HEADLINE" title="12.3.4. Highlighting Results">Section 12.3.4</a> for more details. + </p> + <p> + <code class="literal">ts_headline('{"cat":"raining cats and dogs"}'::jsonb, 'cat')</code> + → <code class="returnvalue">{"cat": "raining <b>cats</b> and dogs"}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.21.1.1.1" class="indexterm"></a> + <code class="function">ts_rank</code> ( + [<span class="optional"> <em class="parameter"><code>weights</code></em> <code class="type">real[]</code>, </span>] + <em class="parameter"><code>vector</code></em> <code class="type">tsvector</code>, + <em class="parameter"><code>query</code></em> <code class="type">tsquery</code> + [<span class="optional">, <em class="parameter"><code>normalization</code></em> <code class="type">integer</code> </span>] ) + → <code class="returnvalue">real</code> + </p> + <p> + Computes a score showing how well + the <em class="parameter"><code>vector</code></em> matches + the <em class="parameter"><code>query</code></em>. See + <a class="xref" href="textsearch-controls.html#TEXTSEARCH-RANKING" title="12.3.3. Ranking Search Results">Section 12.3.3</a> for details. + </p> + <p> + <code class="literal">ts_rank(to_tsvector('raining cats and dogs'), 'cat')</code> + → <code class="returnvalue">0.06079271</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.22.1.1.1" class="indexterm"></a> + <code class="function">ts_rank_cd</code> ( + [<span class="optional"> <em class="parameter"><code>weights</code></em> <code class="type">real[]</code>, </span>] + <em class="parameter"><code>vector</code></em> <code class="type">tsvector</code>, + <em class="parameter"><code>query</code></em> <code class="type">tsquery</code> + [<span class="optional">, <em class="parameter"><code>normalization</code></em> <code class="type">integer</code> </span>] ) + → <code class="returnvalue">real</code> + </p> + <p> + Computes a score showing how well + the <em class="parameter"><code>vector</code></em> matches + the <em class="parameter"><code>query</code></em>, using a cover density + algorithm. See <a class="xref" href="textsearch-controls.html#TEXTSEARCH-RANKING" title="12.3.3. Ranking Search Results">Section 12.3.3</a> for details. + </p> + <p> + <code class="literal">ts_rank_cd(to_tsvector('raining cats and dogs'), 'cat')</code> + → <code class="returnvalue">0.1</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.23.1.1.1" class="indexterm"></a> + <code class="function">ts_rewrite</code> ( <em class="parameter"><code>query</code></em> <code class="type">tsquery</code>, + <em class="parameter"><code>target</code></em> <code class="type">tsquery</code>, + <em class="parameter"><code>substitute</code></em> <code class="type">tsquery</code> ) + → <code class="returnvalue">tsquery</code> + </p> + <p> + Replaces occurrences of <em class="parameter"><code>target</code></em> + with <em class="parameter"><code>substitute</code></em> + within the <em class="parameter"><code>query</code></em>. + See <a class="xref" href="textsearch-features.html#TEXTSEARCH-QUERY-REWRITING" title="12.4.2.1. Query Rewriting">Section 12.4.2.1</a> for details. + </p> + <p> + <code class="literal">ts_rewrite('a & b'::tsquery, 'a'::tsquery, 'foo|bar'::tsquery)</code> + → <code class="returnvalue">'b' & ( 'foo' | 'bar' )</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">ts_rewrite</code> ( <em class="parameter"><code>query</code></em> <code class="type">tsquery</code>, + <em class="parameter"><code>select</code></em> <code class="type">text</code> ) + → <code class="returnvalue">tsquery</code> + </p> + <p> + Replaces portions of the <em class="parameter"><code>query</code></em> according to + target(s) and substitute(s) obtained by executing + a <code class="command">SELECT</code> command. + See <a class="xref" href="textsearch-features.html#TEXTSEARCH-QUERY-REWRITING" title="12.4.2.1. Query Rewriting">Section 12.4.2.1</a> for details. + </p> + <p> + <code class="literal">SELECT ts_rewrite('a & b'::tsquery, 'SELECT t,s FROM aliases')</code> + → <code class="returnvalue">'b' & ( 'foo' | 'bar' )</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.25.1.1.1" class="indexterm"></a> + <code class="function">tsquery_phrase</code> ( <em class="parameter"><code>query1</code></em> <code class="type">tsquery</code>, <em class="parameter"><code>query2</code></em> <code class="type">tsquery</code> ) + → <code class="returnvalue">tsquery</code> + </p> + <p> + Constructs a phrase query that searches + for matches of <em class="parameter"><code>query1</code></em> + and <em class="parameter"><code>query2</code></em> at successive lexemes (same + as <code class="literal"><-></code> operator). + </p> + <p> + <code class="literal">tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'))</code> + → <code class="returnvalue">'fat' <-> 'cat'</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">tsquery_phrase</code> ( <em class="parameter"><code>query1</code></em> <code class="type">tsquery</code>, <em class="parameter"><code>query2</code></em> <code class="type">tsquery</code>, <em class="parameter"><code>distance</code></em> <code class="type">integer</code> ) + → <code class="returnvalue">tsquery</code> + </p> + <p> + Constructs a phrase query that searches + for matches of <em class="parameter"><code>query1</code></em> and + <em class="parameter"><code>query2</code></em> that occur exactly + <em class="parameter"><code>distance</code></em> lexemes apart. + </p> + <p> + <code class="literal">tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'), 10)</code> + → <code class="returnvalue">'fat' <10> 'cat'</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.27.1.1.1" class="indexterm"></a> + <code class="function">tsvector_to_array</code> ( <code class="type">tsvector</code> ) + → <code class="returnvalue">text[]</code> + </p> + <p> + Converts a <code class="type">tsvector</code> to an array of lexemes. + </p> + <p> + <code class="literal">tsvector_to_array('fat:2,4 cat:3 rat:5A'::tsvector)</code> + → <code class="returnvalue">{cat,fat,rat}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.7.2.2.28.1.1.1" class="indexterm"></a> + <code class="function">unnest</code> ( <code class="type">tsvector</code> ) + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>lexeme</code></em> <code class="type">text</code>, + <em class="parameter"><code>positions</code></em> <code class="type">smallint[]</code>, + <em class="parameter"><code>weights</code></em> <code class="type">text</code> ) + </p> + <p> + Expands a <code class="type">tsvector</code> into a set of rows, one per lexeme. + </p> + <p> + <code class="literal">select * from unnest('cat:3 fat:2,4 rat:5A'::tsvector)</code> + → <code class="returnvalue"></code> +</p><pre class="programlisting"> + lexeme | positions | weights +--------+-----------+--------- + cat | {3} | {D} + fat | {2,4} | {D,D} + rat | {5} | {A} +</pre><p> + </p></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.44. Text Search Debugging Functions">Table 9.44</a> + are listed separately because they are not usually used in everyday text + searching operations. They are primarily 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.44. Text Search Debugging Functions</strong></p><div class="table-contents"><table class="table" summary="Text Search Debugging 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.5.8.19.10.2.2.1.1.1.1" class="indexterm"></a> + <code class="function">ts_debug</code> ( + [<span class="optional"> <em class="parameter"><code>config</code></em> <code class="type">regconfig</code>, </span>] + <em class="parameter"><code>document</code></em> <code class="type">text</code> ) + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>alias</code></em> <code class="type">text</code>, + <em class="parameter"><code>description</code></em> <code class="type">text</code>, + <em class="parameter"><code>token</code></em> <code class="type">text</code>, + <em class="parameter"><code>dictionaries</code></em> <code class="type">regdictionary[]</code>, + <em class="parameter"><code>dictionary</code></em> <code class="type">regdictionary</code>, + <em class="parameter"><code>lexemes</code></em> <code class="type">text[]</code> ) + </p> + <p> + Extracts and normalizes tokens from + the <em class="parameter"><code>document</code></em> according to the specified or + default text search configuration, and returns information about how + each token was processed. + See <a class="xref" href="textsearch-debugging.html#TEXTSEARCH-CONFIGURATION-TESTING" title="12.8.1. Configuration Testing">Section 12.8.1</a> for details. + </p> + <p> + <code class="literal">ts_debug('english', 'The Brightest supernovaes')</code> + → <code class="returnvalue">(asciiword,"Word, all ASCII",The,{english_stem},english_stem,{}) ...</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.10.2.2.2.1.1.1" class="indexterm"></a> + <code class="function">ts_lexize</code> ( <em class="parameter"><code>dict</code></em> <code class="type">regdictionary</code>, <em class="parameter"><code>token</code></em> <code class="type">text</code> ) + → <code class="returnvalue">text[]</code> + </p> + <p> + Returns an array of replacement lexemes if the input token is known to + the dictionary, or an empty array if the token is known to the + dictionary but it is a stop word, or NULL if it is not a known word. + See <a class="xref" href="textsearch-debugging.html#TEXTSEARCH-DICTIONARY-TESTING" title="12.8.3. Dictionary Testing">Section 12.8.3</a> for details. + </p> + <p> + <code class="literal">ts_lexize('english_stem', 'stars')</code> + → <code class="returnvalue">{star}</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.10.2.2.3.1.1.1" class="indexterm"></a> + <code class="function">ts_parse</code> ( <em class="parameter"><code>parser_name</code></em> <code class="type">text</code>, + <em class="parameter"><code>document</code></em> <code class="type">text</code> ) + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>tokid</code></em> <code class="type">integer</code>, + <em class="parameter"><code>token</code></em> <code class="type">text</code> ) + </p> + <p> + Extracts tokens from the <em class="parameter"><code>document</code></em> using the + named parser. + See <a class="xref" href="textsearch-debugging.html#TEXTSEARCH-PARSER-TESTING" title="12.8.2. Parser Testing">Section 12.8.2</a> for details. + </p> + <p> + <code class="literal">ts_parse('default', 'foo - bar')</code> + → <code class="returnvalue">(1,foo) ...</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">ts_parse</code> ( <em class="parameter"><code>parser_oid</code></em> <code class="type">oid</code>, + <em class="parameter"><code>document</code></em> <code class="type">text</code> ) + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>tokid</code></em> <code class="type">integer</code>, + <em class="parameter"><code>token</code></em> <code class="type">text</code> ) + </p> + <p> + Extracts tokens from the <em class="parameter"><code>document</code></em> using a + parser specified by OID. + See <a class="xref" href="textsearch-debugging.html#TEXTSEARCH-PARSER-TESTING" title="12.8.2. Parser Testing">Section 12.8.2</a> for details. + </p> + <p> + <code class="literal">ts_parse(3722, 'foo - bar')</code> + → <code class="returnvalue">(1,foo) ...</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.10.2.2.5.1.1.1" class="indexterm"></a> + <code class="function">ts_token_type</code> ( <em class="parameter"><code>parser_name</code></em> <code class="type">text</code> ) + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>tokid</code></em> <code class="type">integer</code>, + <em class="parameter"><code>alias</code></em> <code class="type">text</code>, + <em class="parameter"><code>description</code></em> <code class="type">text</code> ) + </p> + <p> + Returns a table that describes each type of token the named parser can + recognize. + See <a class="xref" href="textsearch-debugging.html#TEXTSEARCH-PARSER-TESTING" title="12.8.2. Parser Testing">Section 12.8.2</a> for details. + </p> + <p> + <code class="literal">ts_token_type('default')</code> + → <code class="returnvalue">(1,asciiword,"Word, all ASCII") ...</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <code class="function">ts_token_type</code> ( <em class="parameter"><code>parser_oid</code></em> <code class="type">oid</code> ) + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>tokid</code></em> <code class="type">integer</code>, + <em class="parameter"><code>alias</code></em> <code class="type">text</code>, + <em class="parameter"><code>description</code></em> <code class="type">text</code> ) + </p> + <p> + Returns a table that describes each type of token a parser specified + by OID can recognize. + See <a class="xref" href="textsearch-debugging.html#TEXTSEARCH-PARSER-TESTING" title="12.8.2. Parser Testing">Section 12.8.2</a> for details. + </p> + <p> + <code class="literal">ts_token_type(3722)</code> + → <code class="returnvalue">(1,asciiword,"Word, all ASCII") ...</code> + </p></td></tr><tr><td class="func_table_entry"><p class="func_signature"> + <a id="id-1.5.8.19.10.2.2.7.1.1.1" class="indexterm"></a> + <code class="function">ts_stat</code> ( <em class="parameter"><code>sqlquery</code></em> <code class="type">text</code> + [<span class="optional">, <em class="parameter"><code>weights</code></em> <code class="type">text</code> </span>] ) + → <code class="returnvalue">setof record</code> + ( <em class="parameter"><code>word</code></em> <code class="type">text</code>, + <em class="parameter"><code>ndoc</code></em> <code class="type">integer</code>, + <em class="parameter"><code>nentry</code></em> <code class="type">integer</code> ) + </p> + <p> + Executes the <em class="parameter"><code>sqlquery</code></em>, which must return a + single <code class="type">tsvector</code> column, and returns statistics about each + distinct lexeme contained in the data. + See <a class="xref" href="textsearch-features.html#TEXTSEARCH-STATISTICS" title="12.4.4. Gathering Document Statistics">Section 12.4.4</a> for details. + </p> + <p> + <code class="literal">ts_stat('SELECT vector FROM apod')</code> + → <code class="returnvalue">(foo,10,15) ...</code> + </p></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" title="9.12. Network Address Functions and Operators">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-uuid.html" title="9.14. UUID Functions">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" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.14. UUID Functions</td></tr></table></div></body></html>
\ No newline at end of file |