9.13. 文字檢索函式及運算子

Table 9.40,Table 9.41andTable 9.42summarize the functions and operators that are provided for full text searching. SeeChapter 12for a detailed explanation ofPostgreSQL's text search facility.

Table 9.40. Text Search Operators

Operator

Return Type

Description

Example

Result

@@

boolean

tsvectormatchestsquery?

to_tsvector('fat cats ate rats') @@ to_tsquery('cat & rat')

t

@@@

boolean

deprecated synonym for@@

to_tsvector('fat cats ate rats') @@@ to_tsquery('cat & rat')

t

`

`

tsvector

concatenatetsvectors

`'a:1 b:2'::tsvector

'c:1 d:2 b:3'::tsvector`

'a':1 'b':2,5 'c':3 'd':4

&&

tsquery

ANDtsquerys together

`'fat

rat'::tsquery && 'cat'::tsquery`

`( 'fat'

'rat' ) & 'cat'`

`

`

tsquery

ORtsquerys together

`'fat

rat'::tsquery

'cat'::tsquery`

`( 'fat'

'rat' )

'cat'`

!!

tsquery

negate atsquery

!! 'cat'::tsquery

!'cat'

<->

tsquery

tsqueryfollowed bytsquery

to_tsquery('fat') <-> to_tsquery('rat')

'fat' <-> 'rat'

@>

boolean

tsquerycontains another ?

'cat'::tsquery @> 'cat & rat'::tsquery

f

<@

boolean

tsqueryis contained in ?

'cat'::tsquery <@ 'cat & rat'::tsquery

t

Note

Thetsquerycontainment operators consider only the lexemes listed in the two queries, ignoring the combining operators.

In addition to the operators shown in the table, the ordinary B-tree comparison operators (=,<, etc) are defined for typestsvectorandtsquery. These are not very useful for text searching but allow, for example, unique indexes to be built on columns of these types.

Table 9.41. Text Search Functions

Function

Return Type

Description

Example

Result

array_to_tsvector(text[])

tsvector

convert array of lexemes totsvector

array_to_tsvector('{fat,cat,rat}'::text[])

'cat' 'fat' 'rat'

get_current_ts_config()

regconfig

get default text search configuration

get_current_ts_config()

english

length(tsvector)

integer

number of lexemes intsvector

length('fat:2,4 cat:3 rat:5A'::tsvector)

3

numnode(tsquery)

integer

number of lexemes plus operators intsquery

`numnode('(fat & rat)

cat'::tsquery)`

5

plainto_tsquery([configregconfig,]querytext)

tsquery

producetsqueryignoring punctuation

plainto_tsquery('english', 'The Fat Rats')

'fat' & 'rat'

phraseto_tsquery([configregconfig,]querytext)

tsquery

producetsquerythat searches for a phrase, ignoring punctuation

phraseto_tsquery('english', 'The Fat Rats')

'fat' <-> 'rat'

querytree(querytsquery)

text

get indexable part of atsquery

querytree('foo & ! bar'::tsquery)

'foo'

setweight(vectortsvector,weight"char")

tsvector

assignweight_to each element ofvector_

setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A')

'cat':3A 'fat':2A,4A 'rat':5A

setweight(vectortsvector,weight"char",lexemestext[])

tsvector

assignweight_to elements ofvectorthat are listed inlexemes_

setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A', '{cat,rat}')

'cat':3A 'fat':2,4 'rat':5A

strip(tsvector)

tsvector

remove positions and weights fromtsvector

strip('fat:2,4 cat:3 rat:5A'::tsvector)

'cat' 'fat' 'rat'

to_tsquery([configregconfig,]querytext)

tsquery

normalize words and convert totsquery

to_tsquery('english', 'The & Fat & Rats')

'fat' & 'rat'

to_tsvector([configregconfig,]documenttext)

tsvector

reduce document text totsvector

to_tsvector('english', 'The Fat Rats')

'fat':2 'rat':3

to_tsvector([configregconfig,]documentjson(b))

tsvector

reduce each string value in the document to atsvector, and then concatentate those in document order to produce a singletsvector

to_tsvector('english', '{"a": "The Fat Rats"}'::json)

'fat':2 'rat':3

ts_delete(vectortsvector,lexemetext)

tsvector

remove givenlexeme_fromvector_

ts_delete('fat:2,4 cat:3 rat:5A'::tsvector, 'fat')

'cat':3 'rat':5A

ts_delete(vectortsvector,lexemestext[])

tsvector

remove any occurrence of lexemes inlexemes_fromvector_

ts_delete('fat:2,4 cat:3 rat:5A'::tsvector, ARRAY['fat','rat'])

'cat':3

ts_filter(vectortsvector,weights"char"[])

tsvector

select only elements with givenweights_fromvector_

ts_filter('fat:2,4 cat:3b rat:5A'::tsvector, '{a,b}')

'cat':3B 'rat':5A

ts_headline([configregconfig,]documenttext,querytsquery[,optionstext])

text

display a query match

ts_headline('x y z', 'z'::tsquery)

x y <b>z</b>

ts_headline([configregconfig,]documentjson(b),querytsquery[,optionstext])

text

display a query match

ts_headline('{"a":"x y z"}'::json, 'z'::tsquery)

{"a":"x y <b>z</b>"}

ts_rank([weightsfloat4[],]vectortsvector,querytsquery[,normalizationinteger])

float4

rank document for query

ts_rank(textsearch, query)

0.818

ts_rank_cd([weightsfloat4[],]vectortsvector,querytsquery[,normalizationinteger])

float4

rank document for query using cover density

ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', textsearch, query)

2.01317

ts_rewrite(querytsquery,targettsquery,substitutetsquery)

tsquery

replacetarget_withsubstitute_within query

`ts_rewrite('a & b'::tsquery, 'a'::tsquery, 'foo

bar'::tsquery)`

`'b' & ( 'foo'

'bar' )`

ts_rewrite(querytsquery,selecttext)

tsquery

replace using targets and substitutes from aSELECTcommand

SELECT ts_rewrite('a & b'::tsquery, 'SELECT t,s FROM aliases')

`'b' & ( 'foo'

'bar' )`

tsquery_phrase(query1tsquery,query2tsquery)

tsquery

make query that searches forquery1_followed byquery2_(same as<->operator)

tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'))

'fat' <-> 'cat'

tsquery_phrase(query1tsquery,query2tsquery,distanceinteger)

tsquery

make query that searches forquery1_followed byquery2at distancedistance_

tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'), 10)

'fat' <10> 'cat'

tsvector_to_array(tsvector)

text[]

converttsvectorto array of lexemes

tsvector_to_array('fat:2,4 cat:3 rat:5A'::tsvector)

{cat,fat,rat}

tsvector_update_trigger()

trigger

trigger function for automatictsvectorcolumn update

CREATE TRIGGER ... tsvector_update_trigger(tsvcol, 'pg_catalog.swedish', title, body)

tsvector_update_trigger_column()

trigger

trigger function for automatictsvectorcolumn update

CREATE TRIGGER ... tsvector_update_trigger_column(tsvcol, configcol, title, body)

unnest(tsvector, OUTlexemetext, OUTpositionssmallint[], OUTweightstext)

setof record

expand a tsvector to a set of rows

unnest('fat:2,4 cat:3 rat:5A'::tsvector)

(cat,{3},{D}) ...

Note

All the text search functions that accept an optionalregconfigargument will use the configuration specified bydefault_text_search_configwhen that argument is omitted.

The functions inTable 9.42are 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.

Table 9.42. Text Search Debugging Functions

Function

Return Type

Description

Example

Result

ts_debug([configregconfig,]documenttext, OUTaliastext, OUTdescriptiontext, OUTtokentext, OUTdictionariesregdictionary[], OUTdictionaryregdictionary, OUTlexemestext[])

setof record

test a configuration

ts_debug('english', 'The Brightest supernovaes')

(asciiword,"Word, all ASCII",The,{english_stem},english_stem,{}) ...

ts_lexize(dictregdictionary,tokentext)

text[]

test a dictionary

ts_lexize('english_stem', 'stars')

{star}

ts_parse(parser_nametext,documenttext, OUTtokidinteger, OUTtokentext)

setof record

test a parser

ts_parse('default', 'foo - bar')

(1,foo) ...

ts_parse(parser_oidoid,documenttext, OUTtokidinteger, OUTtokentext)

setof record

test a parser

ts_parse(3722, 'foo - bar')

(1,foo) ...

ts_token_type(parser_nametext, OUTtokidinteger, OUTaliastext, OUTdescriptiontext)

setof record

get token types defined by parser

ts_token_type('default')

(1,asciiword,"Word, all ASCII") ...

ts_token_type(parser_oidoid, OUTtokidinteger, OUTaliastext, OUTdescriptiontext)

setof record

get token types defined by parser

ts_token_type(3722)

(1,asciiword,"Word, all ASCII") ...

ts_stat(sqlquerytext, [weightstext,] OUTwordtext, OUTndocinteger, OUTnentryinteger)

setof record

get statistics of atsvectorcolumn

ts_stat('SELECT vector from apod')

(foo,10,15) ...