Home Explore Blog CI



postgresql

22th chunk of `doc/src/sgml/textsearch.sgml`
a7f07148e132e1c3ed0b423dea2ada38c83ab5e9eb1561a50000000100000fa2
 <term>
      <literal><type>tsquery</type> &lt;-&gt; <type>tsquery</type></literal>
     </term>

     <listitem>
      <para>
       Returns a query that searches for a match to the first given query
       immediately followed by a match to the second given query, using
       the <literal>&lt;-&gt;</literal> (FOLLOWED BY)
       <type>tsquery</type> operator.  For example:

<screen>
SELECT to_tsquery('fat') &lt;-&gt; to_tsquery('cat | rat');
          ?column?
----------------------------
 'fat' &lt;-&gt; ( 'cat' | 'rat' )
</screen>
      </para>
     </listitem>

    </varlistentry>

    <varlistentry>

     <term>
     <indexterm>
      <primary>tsquery_phrase</primary>
     </indexterm>

      <literal>tsquery_phrase(<replaceable class="parameter">query1</replaceable> <type>tsquery</type>, <replaceable class="parameter">query2</replaceable> <type>tsquery</type> [, <replaceable class="parameter">distance</replaceable> <type>integer</type> ]) returns <type>tsquery</type></literal>
     </term>

     <listitem>
      <para>
       Returns a query that searches for a match to the first given query
       followed by a match to the second given query at a distance of exactly
       <replaceable>distance</replaceable> lexemes, using
       the <literal>&lt;<replaceable>N</replaceable>&gt;</literal>
       <type>tsquery</type> operator.  For example:

<screen>
SELECT tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'), 10);
  tsquery_phrase
------------------
 'fat' &lt;10&gt; 'cat'
</screen>
      </para>
     </listitem>

    </varlistentry>

    <varlistentry>

     <term>
     <indexterm>
      <primary>numnode</primary>
     </indexterm>

      <literal>numnode(<replaceable class="parameter">query</replaceable> <type>tsquery</type>) returns <type>integer</type></literal>
     </term>

     <listitem>
      <para>
       Returns the number of nodes (lexemes plus operators) in a
       <type>tsquery</type>. This function is useful
       to determine if the <replaceable>query</replaceable> is meaningful
       (returns &gt; 0), or contains only stop words (returns 0).
       Examples:

<screen>
SELECT numnode(plainto_tsquery('the any'));
NOTICE:  query contains only stopword(s) or doesn't contain lexeme(s), ignored
 numnode
---------
       0

SELECT numnode('foo &amp; bar'::tsquery);
 numnode
---------
       3
</screen>
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>

     <term>
     <indexterm>
      <primary>querytree</primary>
     </indexterm>

      <literal>querytree(<replaceable class="parameter">query</replaceable> <type>tsquery</type>) returns <type>text</type></literal>
     </term>

     <listitem>
      <para>
       Returns the portion of a <type>tsquery</type> that can be used for
       searching an index.  This function is useful for detecting
       unindexable queries, for example those containing only stop words
       or only negated terms.  For example:

<screen>
SELECT querytree(to_tsquery('defined'));
 querytree
-----------
 'defin'

SELECT querytree(to_tsquery('!defined'));
 querytree
-----------
 T
</screen>
      </para>
     </listitem>
    </varlistentry>

   </variablelist>

   <sect3 id="textsearch-query-rewriting">
    <title>Query Rewriting</title>

    <indexterm zone="textsearch-query-rewriting">
     <primary>ts_rewrite</primary>
    </indexterm>

    <para>
     The <function>ts_rewrite</function> family of functions search a
     given <type>tsquery</type> for occurrences of a target
     subquery, and replace each occurrence with a
     substitute subquery.  In essence this operation is a
     <type>tsquery</type>-specific version of substring replacement.
     A target and substitute combination can be
     thought of as a <firstterm>query rewrite rule</firstterm>.  A collection
     of such rewrite rules can be a powerful search aid.
     For example, you can expand the search using synonyms
     (e.g., <literal>new york</literal>, <literal>big apple</literal>,

Title: Advanced tsquery Manipulation: Proximity Searches, Node Counting, and Index Optimization
Summary
This section describes several functions for advanced manipulation of tsquery values in PostgreSQL. It details how to use tsquery_phrase to search for terms within a specific distance of each other and how to use the FOLLOWED BY operator. The numnode function counts the number of nodes (lexemes and operators) in a tsquery, helping to identify queries containing only stop words. Finally, querytree extracts the indexable portion of a tsquery, useful for detecting unindexable queries containing only stop words or negated terms. The section then introduces the concept of query rewriting using the ts_rewrite family of functions.