12.4. 延伸功能

This section describes additional functions and operators that are useful in connection with text search.

12.4.1. Manipulating Documents

Section 12.3.1showed how raw textual documents can be converted intotsvectorvalues.PostgreSQLalso provides functions and operators that can be used to manipulate documents that are already intsvectorform.

tsvector

||

tsvector

Thetsvectorconcatenation operator returns a vector which combines the lexemes and positional information of the two vectors given as arguments. Positions and weight labels are retained during the concatenation. Positions appearing in the right-hand vector are offset by the largest position mentioned in the left-hand vector, so that the result is nearly equivalent to the result of performingto_tsvectoron the concatenation of the two original document strings. (The equivalence is not exact, because any stop-words removed from the end of the left-hand argument will not affect the result, whereas they would have affected the positions of the lexemes in the right-hand argument if textual concatenation were used.)

One advantage of using concatenation in the vector form, rather than concatenating text before applyingto_tsvector, is that you can use different configurations to parse different sections of the document. Also, because thesetweightfunction marks all lexemes of the given vector the same way, it is necessary to parse the text and dosetweightbefore concatenating if you want to label different parts of the document with different weights.

setweight(

vector

tsvector

,

weight

"char"

) returns

tsvector

setweightreturns a copy of the input vector in which every position has been labeled with the givenweight, eitherA,B,C, orD. (Dis the default for new vectors and as such is not displayed on output.) These labels are retained when vectors are concatenated, allowing words from different parts of a document to be weighted differently by ranking functions.

Note that weight labels apply topositions, notlexemes. If the input vector has been stripped of positions thensetweightdoes nothing.

length(

vector

tsvector

) returns

integer

Returns the number of lexemes stored in the vector.

strip(

vector

tsvector

) returns

tsvector

Returns a vector that lists the same lexemes as the given vector, but lacks any position or weight information. The result is usually much smaller than an unstripped vector, but it is also less useful. Relevance ranking does not work as well on stripped vectors as unstripped ones. Also, the<->(FOLLOWED BY)tsqueryoperator will never match stripped input, since it cannot determine the distance between lexeme occurrences.

A full list oftsvector-related functions is available inTable 9.41.

12.4.2. Manipulating Queries

Section 12.3.2showed how raw textual queries can be converted intotsqueryvalues.PostgreSQLalso provides functions and operators that can be used to manipulate queries that are already intsqueryform.

tsquery

&

&

tsquery

Returns the AND-combination of the two given queries.

tsquery

||

tsquery

Returns the OR-combination of the two given queries.

!!

tsquery

Returns the negation (NOT) of the given query.

tsquery

<

-

>

tsquery

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<->(FOLLOWED BY)tsqueryoperator. For example:

  1. SELECT to_tsquery('fat') 
  2. <
  3. -
  4. >
  5.  to_tsquery('cat | rat');
  6.              ?column?
  7. -----------------------------------
  8.  'fat' 
  9. <
  10. -
  11. >
  12.  'cat' | 'fat' 
  13. <
  14. -
  15. >
  16.  'rat'

tsquery_phrase(

query1

tsquery

,

query2

tsquery

[,

distance

integer

]) returns

tsquery

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 atdistance_lexemes, using the<N_>tsqueryoperator. For example:

  1. SELECT tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'), 10);
  2.   tsquery_phrase
  3. ------------------
  4.  'fat' 
  5. <
  6. 10
  7. >
  8.  'cat'

numnode(

query

tsquery

) returns

integer

Returns the number of nodes (lexemes plus operators) in atsquery. This function is useful to determine if the_query_is meaningful (returns > 0), or contains only stop words (returns 0). Examples:

  1. SELECT numnode(plainto_tsquery('the any'));
  2. NOTICE:  query contains only stopword(s) or doesn't contain lexeme(s), ignored
  3.  numnode
  4. ---------
  5.        0
  7. SELECT numnode('foo 
  8. &
  9.  bar'::tsquery);
  10.  numnode
  11. ---------
  12.        3

querytree(

query

tsquery

) returns

text

Returns the portion of atsquerythat 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:

  1. SELECT querytree(to_tsquery('!defined'));
  2.  querytree
  3. -----------

12.4.2.1. Query Rewriting

Thets_rewritefamily of functions search a giventsqueryfor occurrences of a target subquery, and replace each occurrence with a substitute subquery. In essence this operation is atsquery-specific version of substring replacement. A target and substitute combination can be thought of as aquery rewrite rule. A collection of such rewrite rules can be a powerful search aid. For example, you can expand the search using synonyms (e.g.,new york,big apple,nyc,gotham) or narrow the search to direct the user to some hot topic. There is some overlap in functionality between this feature and thesaurus dictionaries (Section 12.6.4). However, you can modify a set of rewrite rules on-the-fly without reindexing, whereas updating a thesaurus requires reindexing to be effective.

ts_rewrite (

query

tsquery

,

target

tsquery

,

substitute

tsquery

) returns

tsquery

This form ofts_rewritesimply applies a single rewrite rule:target_is replaced bysubstitutewherever it appears inquery_. For example:

  1. SELECT ts_rewrite('a 
  2. &
  3.  b'::tsquery, 'a'::tsquery, 'c'::tsquery);
  4.  ts_rewrite
  5. ------------
  6.  'b' 
  7. &
  8.  'c'

ts_rewrite (

query

tsquery

,

select

text

) returns

tsquery

This form ofts_rewriteaccepts a startingquery_and a SQLselectcommand, which is given as a text string. Theselectmust yield two columns oftsquerytype. For each row of theselectresult, occurrences of the first column value (the target) are replaced by the second column value (the substitute) within the currentquery_value. For example:

  1. CREATE TABLE aliases (t tsquery PRIMARY KEY, s tsquery);
  2. INSERT INTO aliases VALUES('a', 'c');
  4. SELECT ts_rewrite('a 
  5. &
  6.  b'::tsquery, 'SELECT t,s FROM aliases');
  7.  ts_rewrite
  8. ------------
  9.  'b' 
  10. &
  11.  'c'

Note that when multiple rewrite rules are applied in this way, the order of application can be important; so in practice you will want the source query toORDER BYsome ordering key.

Let’s consider a real-life astronomical example. We’ll expand querysupernovaeusing table-driven rewriting rules:

  1. CREATE TABLE aliases (t tsquery primary key, s tsquery);
  2. INSERT INTO aliases VALUES(to_tsquery('supernovae'), to_tsquery('supernovae|sn'));
  4. SELECT ts_rewrite(to_tsquery('supernovae 
  5. &
  6.  crab'), 'SELECT * FROM aliases');
  7.            ts_rewrite            
  8. ---------------------------------
  9.  'crab' 
  10. &
  11.  ( 'supernova' | 'sn' )

We can change the rewriting rules just by updating the table:

  1. UPDATE aliases
  2. SET s = to_tsquery('supernovae|sn 
  3. &
  4.  !nebulae')
  5. WHERE t = to_tsquery('supernovae');
  7. SELECT ts_rewrite(to_tsquery('supernovae 
  8. &
  9.  crab'), 'SELECT * FROM aliases');
  10.                  ts_rewrite                  
  11. ---------------------------------------------
  12.  'crab' 
  13. &
  14.  ( 'supernova' | 'sn' 
  15. &
  16.  !'nebula' )

Rewriting can be slow when there are many rewriting rules, since it checks every rule for a possible match. To filter out obvious non-candidate rules we can use the containment operators for thetsquerytype. In the example below, we select only those rules which might match the original query:

  1. SELECT ts_rewrite('a 
  2. &
  3.  b'::tsquery,
  4.                   'SELECT t,s FROM aliases WHERE ''a 
  5. &
  6.  b''::tsquery @
  7. >
  8.  t');
  9.  ts_rewrite
  10. ------------
  11.  'b' 
  12. &
  13.  'c'

12.4.3. Triggers for Automatic Updates

When using a separate column to store thetsvectorrepresentation of your documents, it is necessary to create a trigger to update thetsvectorcolumn when the document content columns change. Two built-in trigger functions are available for this, or you can write your own.

  1. tsvector_update_trigger(
  2. tsvector_column_name
  3. , 
  4. config_name
  5. , 
  6. text_column_name
  7.  [
  8. , ... 
  9. ])
  10. tsvector_update_trigger_column(
  11. tsvector_column_name
  12. , 
  13. config_column_name
  14. , 
  15. text_column_name
  16.  [
  17. , ... 
  18. ])

These trigger functions automatically compute atsvectorcolumn from one or more textual columns, under the control of parameters specified in theCREATE TRIGGERcommand. An example of their use is:

  1. CREATE TABLE messages (
  2.     title       text,
  3.     body        text,
  4.     tsv         tsvector
  5. );
  7. CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
  8. ON messages FOR EACH ROW EXECUTE PROCEDURE
  9. tsvector_update_trigger(tsv, 'pg_catalog.english', title, body);
  11. INSERT INTO messages VALUES('title here', 'the body text is here');
  13. SELECT * FROM messages;
  14.    title    |         body          |            tsv             
  15. ------------+-----------------------+----------------------------
  16.  title here | the body text is here | 'bodi':4 'text':5 'titl':1
  18. SELECT title, body FROM messages WHERE tsv @@ to_tsquery('title 
  19. &
  20.  body');
  21.    title    |         body          
  22. ------------+-----------------------
  23.  title here | the body text is here

Having created this trigger, any change intitleorbodywill automatically be reflected intotsv, without the application having to worry about it.

The first trigger argument must be the name of thetsvectorcolumn to be updated. The second argument specifies the text search configuration to be used to perform the conversion. Fortsvector_update_trigger, the configuration name is simply given as the second trigger argument. It must be schema-qualified as shown above, so that the trigger behavior will not change with changes insearch_path. Fortsvector_update_trigger_column, the second trigger argument is the name of another table column, which must be of typeregconfig. This allows a per-row selection of configuration to be made. The remaining argument(s) are the names of textual columns (of typetext,varchar, orchar). These will be included in the document in the order given. NULL values will be skipped (but the other columns will still be indexed).

A limitation of these built-in triggers is that they treat all the input columns alike. To process columns differently — for example, to weight title differently from body — it is necessary to write a custom trigger. Here is an example usingPL/pgSQLas the trigger language:

  1. CREATE FUNCTION messages_trigger() RETURNS trigger AS $$
  2. begin
  3.   new.tsv :=
  4.      setweight(to_tsvector('pg_catalog.english', coalesce(new.title,'')), 'A') ||
  5.      setweight(to_tsvector('pg_catalog.english', coalesce(new.body,'')), 'D');
  6.   return new;
  7. end
  8. $$ LANGUAGE plpgsql;
  10. CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
  11.     ON messages FOR EACH ROW EXECUTE PROCEDURE messages_trigger();

Keep in mind that it is important to specify the configuration name explicitly when creatingtsvectorvalues inside triggers, so that the column’s contents will not be affected by changes todefault_text_search_config. Failure to do this is likely to lead to problems such as search results changing after a dump and reload.

12.4.4. Gathering Document Statistics

The functionts_statis useful for checking your configuration and for finding stop-word candidates.

  1. ts_stat(
  2. sqlquery
  3. text
  4. , [
  5. weights
  6. text
  7. , 
  8. ]
  9.         OUT 
  10. word
  11. text
  12. , OUT 
  13. ndoc
  14. integer
  15. ,
  16.         OUT 
  17. nentry
  18. integer
  19. ) returns 
  20. setof record

_sqlquery_is a text value containing an SQL query which must return a singletsvectorcolumn.ts_statexecutes the query and returns statistics about each distinct lexeme (word) contained in thetsvectordata. The columns returned are

  • wordtext— the value of a lexeme
  • ndocinteger— number of documents (tsvectors) the word occurred in
  • nentryinteger— total number of occurrences of the word

If_weights_is supplied, only occurrences having one of those weights are counted.

For example, to find the ten most frequent words in a document collection:

  1. SELECT * FROM ts_stat('SELECT vector FROM apod')
  2. ORDER BY nentry DESC, ndoc DESC, word
  3. LIMIT 10;

The same, but counting only word occurrences with weightAorB:

  1. SELECT * FROM ts_stat('SELECT vector FROM apod', 'ab')
  2. ORDER BY nentry DESC, ndoc DESC, word
  3. LIMIT 10;