1-- 2-- Sanity checks for text search catalogs 3-- 4-- NB: we assume the oidjoins test will have caught any dangling links, 5-- that is OID or REGPROC fields that are not zero and do not match some 6-- row in the linked-to table. However, if we want to enforce that a link 7-- field can't be 0, we have to check it here. 8 9-- Find unexpected zero link entries 10 11SELECT oid, prsname 12FROM pg_ts_parser 13WHERE prsnamespace = 0 OR prsstart = 0 OR prstoken = 0 OR prsend = 0 OR 14 -- prsheadline is optional 15 prslextype = 0; 16 17SELECT oid, dictname 18FROM pg_ts_dict 19WHERE dictnamespace = 0 OR dictowner = 0 OR dicttemplate = 0; 20 21SELECT oid, tmplname 22FROM pg_ts_template 23WHERE tmplnamespace = 0 OR tmpllexize = 0; -- tmplinit is optional 24 25SELECT oid, cfgname 26FROM pg_ts_config 27WHERE cfgnamespace = 0 OR cfgowner = 0 OR cfgparser = 0; 28 29SELECT mapcfg, maptokentype, mapseqno 30FROM pg_ts_config_map 31WHERE mapcfg = 0 OR mapdict = 0; 32 33-- Look for pg_ts_config_map entries that aren't one of parser's token types 34SELECT * FROM 35 ( SELECT oid AS cfgid, (ts_token_type(cfgparser)).tokid AS tokid 36 FROM pg_ts_config ) AS tt 37RIGHT JOIN pg_ts_config_map AS m 38 ON (tt.cfgid=m.mapcfg AND tt.tokid=m.maptokentype) 39WHERE 40 tt.cfgid IS NULL OR tt.tokid IS NULL; 41 42-- test basic text search behavior without indexes, then with 43 44SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 45SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; 46SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; 47SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; 48SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; 49SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; 50SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; 51SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); 52SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; 53SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; 54SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; 55SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; 56SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; 57SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; 58SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; 59SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; 60SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; 61SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; 62SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; 63SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; 64 65create index wowidx on test_tsvector using gist (a); 66 67SET enable_seqscan=OFF; 68SET enable_indexscan=ON; 69SET enable_bitmapscan=OFF; 70 71explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 72 73SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 74SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; 75SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; 76SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; 77SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; 78SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; 79SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; 80SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); 81SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; 82SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; 83SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; 84SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; 85SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; 86SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; 87SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; 88SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; 89SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; 90SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; 91SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; 92SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; 93 94SET enable_indexscan=OFF; 95SET enable_bitmapscan=ON; 96 97explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 98 99SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 100SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; 101SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; 102SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; 103SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; 104SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; 105SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; 106SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); 107SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; 108SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; 109SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; 110SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; 111SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; 112SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; 113SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; 114SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; 115SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; 116SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; 117SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; 118SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; 119 120RESET enable_seqscan; 121RESET enable_indexscan; 122RESET enable_bitmapscan; 123 124DROP INDEX wowidx; 125 126CREATE INDEX wowidx ON test_tsvector USING gin (a); 127 128SET enable_seqscan=OFF; 129-- GIN only supports bitmapscan, so no need to test plain indexscan 130 131explain (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 132 133SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; 134SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh'; 135SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt'; 136SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt'; 137SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)'; 138SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)'; 139SELECT count(*) FROM test_tsvector WHERE a @@ 'w:*|q:*'; 140SELECT count(*) FROM test_tsvector WHERE a @@ any ('{wr,qh}'); 141SELECT count(*) FROM test_tsvector WHERE a @@ 'no_such_lexeme'; 142SELECT count(*) FROM test_tsvector WHERE a @@ '!no_such_lexeme'; 143SELECT count(*) FROM test_tsvector WHERE a @@ 'pl <-> yh'; 144SELECT count(*) FROM test_tsvector WHERE a @@ 'yh <-> pl'; 145SELECT count(*) FROM test_tsvector WHERE a @@ 'qe <2> qt'; 146SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> yh'; 147SELECT count(*) FROM test_tsvector WHERE a @@ '!pl <-> !yh'; 148SELECT count(*) FROM test_tsvector WHERE a @@ '!yh <-> pl'; 149SELECT count(*) FROM test_tsvector WHERE a @@ '!qe <2> qt'; 150SELECT count(*) FROM test_tsvector WHERE a @@ '!(pl <-> yh)'; 151SELECT count(*) FROM test_tsvector WHERE a @@ '!(yh <-> pl)'; 152SELECT count(*) FROM test_tsvector WHERE a @@ '!(qe <2> qt)'; 153 154RESET enable_seqscan; 155 156INSERT INTO test_tsvector VALUES ('???', 'DFG:1A,2B,6C,10 FGH'); 157SELECT * FROM ts_stat('SELECT a FROM test_tsvector') ORDER BY ndoc DESC, nentry DESC, word LIMIT 10; 158SELECT * FROM ts_stat('SELECT a FROM test_tsvector', 'AB') ORDER BY ndoc DESC, nentry DESC, word; 159 160--dictionaries and to_tsvector 161 162SELECT ts_lexize('english_stem', 'skies'); 163SELECT ts_lexize('english_stem', 'identity'); 164 165SELECT * FROM ts_token_type('default'); 166 167SELECT * FROM ts_parse('default', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net teodor@123-stack.net 123_teodor@stack.net 123-teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 168/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234 169<i <b> wow < jqw <> qwerty'); 170 171SELECT to_tsvector('english', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net teodor@123-stack.net 123_teodor@stack.net 123-teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 172/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234 173<i <b> wow < jqw <> qwerty'); 174 175SELECT length(to_tsvector('english', '345 qwe@efd.r '' http://www.com/ http://aew.werc.ewr/?ad=qwe&dw 1aew.werc.ewr/?ad=qwe&dw 2aew.werc.ewr http://3aew.werc.ewr/?ad=qwe&dw http://4aew.werc.ewr http://5aew.werc.ewr:8100/? ad=qwe&dw 6aew.werc.ewr:8100/?ad=qwe&dw 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32 +4.0e-10 qwe qwe qwqwe 234.435 455 5.005 teodor@stack.net teodor@123-stack.net 123_teodor@stack.net 123-teodor@stack.net qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>"> 176/usr/local/fff /awdf/dwqe/4325 rewt/ewr wefjn /wqe-324/ewr gist.h gist.h.c gist.c. readline 4.2 4.2. 4.2, readline-4.2 readline-4.2. 234 177<i <b> wow < jqw <> qwerty')); 178 179-- ts_debug 180 181SELECT * from ts_debug('english', '<myns:foo-bar_baz.blurfl>abc&nm1;def©ghiõjkl</myns:foo-bar_baz.blurfl>'); 182 183-- check parsing of URLs 184SELECT * from ts_debug('english', 'http://www.harewoodsolutions.co.uk/press.aspx</span>'); 185SELECT * from ts_debug('english', 'http://aew.wer0c.ewr/id?ad=qwe&dw<span>'); 186SELECT * from ts_debug('english', 'http://5aew.werc.ewr:8100/?'); 187SELECT * from ts_debug('english', '5aew.werc.ewr:8100/?xx'); 188 189-- to_tsquery 190 191SELECT to_tsquery('english', 'qwe & sKies '); 192SELECT to_tsquery('simple', 'qwe & sKies '); 193SELECT to_tsquery('english', '''the wether'':dc & '' sKies '':BC '); 194SELECT to_tsquery('english', 'asd&(and|fghj)'); 195SELECT to_tsquery('english', '(asd&and)|fghj'); 196SELECT to_tsquery('english', '(asd&!and)|fghj'); 197SELECT to_tsquery('english', '(the|and&(i&1))&fghj'); 198 199SELECT plainto_tsquery('english', 'the and z 1))& fghj'); 200SELECT plainto_tsquery('english', 'foo bar') && plainto_tsquery('english', 'asd'); 201SELECT plainto_tsquery('english', 'foo bar') || plainto_tsquery('english', 'asd fg'); 202SELECT plainto_tsquery('english', 'foo bar') || !!plainto_tsquery('english', 'asd fg'); 203SELECT plainto_tsquery('english', 'foo bar') && 'asd | fg'; 204 205-- Check stop word deletion, a and s are stop-words 206SELECT to_tsquery('english', '!(a & !b) & c'); 207SELECT to_tsquery('english', '!(a & !b)'); 208 209SELECT to_tsquery('english', '(1 <-> 2) <-> a'); 210SELECT to_tsquery('english', '(1 <-> a) <-> 2'); 211SELECT to_tsquery('english', '(a <-> 1) <-> 2'); 212SELECT to_tsquery('english', 'a <-> (1 <-> 2)'); 213SELECT to_tsquery('english', '1 <-> (a <-> 2)'); 214SELECT to_tsquery('english', '1 <-> (2 <-> a)'); 215 216SELECT to_tsquery('english', '(1 <-> 2) <3> a'); 217SELECT to_tsquery('english', '(1 <-> a) <3> 2'); 218SELECT to_tsquery('english', '(a <-> 1) <3> 2'); 219SELECT to_tsquery('english', 'a <3> (1 <-> 2)'); 220SELECT to_tsquery('english', '1 <3> (a <-> 2)'); 221SELECT to_tsquery('english', '1 <3> (2 <-> a)'); 222 223SELECT to_tsquery('english', '(1 <3> 2) <-> a'); 224SELECT to_tsquery('english', '(1 <3> a) <-> 2'); 225SELECT to_tsquery('english', '(a <3> 1) <-> 2'); 226SELECT to_tsquery('english', 'a <-> (1 <3> 2)'); 227SELECT to_tsquery('english', '1 <-> (a <3> 2)'); 228SELECT to_tsquery('english', '1 <-> (2 <3> a)'); 229 230SELECT to_tsquery('english', '((a <-> 1) <-> 2) <-> s'); 231SELECT to_tsquery('english', '(2 <-> (a <-> 1)) <-> s'); 232SELECT to_tsquery('english', '((1 <-> a) <-> 2) <-> s'); 233SELECT to_tsquery('english', '(2 <-> (1 <-> a)) <-> s'); 234SELECT to_tsquery('english', 's <-> ((a <-> 1) <-> 2)'); 235SELECT to_tsquery('english', 's <-> (2 <-> (a <-> 1))'); 236SELECT to_tsquery('english', 's <-> ((1 <-> a) <-> 2)'); 237SELECT to_tsquery('english', 's <-> (2 <-> (1 <-> a))'); 238 239SELECT to_tsquery('english', '((a <-> 1) <-> s) <-> 2'); 240SELECT to_tsquery('english', '(s <-> (a <-> 1)) <-> 2'); 241SELECT to_tsquery('english', '((1 <-> a) <-> s) <-> 2'); 242SELECT to_tsquery('english', '(s <-> (1 <-> a)) <-> 2'); 243SELECT to_tsquery('english', '2 <-> ((a <-> 1) <-> s)'); 244SELECT to_tsquery('english', '2 <-> (s <-> (a <-> 1))'); 245SELECT to_tsquery('english', '2 <-> ((1 <-> a) <-> s)'); 246SELECT to_tsquery('english', '2 <-> (s <-> (1 <-> a))'); 247 248SELECT to_tsquery('english', 'foo <-> (a <-> (the <-> bar))'); 249SELECT to_tsquery('english', '((foo <-> a) <-> the) <-> bar'); 250SELECT to_tsquery('english', 'foo <-> a <-> the <-> bar'); 251SELECT phraseto_tsquery('english', 'PostgreSQL can be extended by the user in many ways'); 252 253 254SELECT ts_rank_cd(to_tsvector('english', ' 255Day after day, day after day, 256 We stuck, nor breath nor motion, 257As idle as a painted Ship 258 Upon a painted Ocean. 259Water, water, every where 260 And all the boards did shrink; 261Water, water, every where, 262 Nor any drop to drink. 263S. T. Coleridge (1772-1834) 264'), to_tsquery('english', 'paint&water')); 265 266SELECT ts_rank_cd(to_tsvector('english', ' 267Day after day, day after day, 268 We stuck, nor breath nor motion, 269As idle as a painted Ship 270 Upon a painted Ocean. 271Water, water, every where 272 And all the boards did shrink; 273Water, water, every where, 274 Nor any drop to drink. 275S. T. Coleridge (1772-1834) 276'), to_tsquery('english', 'breath&motion&water')); 277 278SELECT ts_rank_cd(to_tsvector('english', ' 279Day after day, day after day, 280 We stuck, nor breath nor motion, 281As idle as a painted Ship 282 Upon a painted Ocean. 283Water, water, every where 284 And all the boards did shrink; 285Water, water, every where, 286 Nor any drop to drink. 287S. T. Coleridge (1772-1834) 288'), to_tsquery('english', 'ocean')); 289 290SELECT ts_rank_cd(to_tsvector('english', ' 291Day after day, day after day, 292 We stuck, nor breath nor motion, 293As idle as a painted Ship 294 Upon a painted Ocean. 295Water, water, every where 296 And all the boards did shrink; 297Water, water, every where, 298 Nor any drop to drink. 299S. T. Coleridge (1772-1834) 300'), to_tsquery('english', 'painted <-> Ship')); 301 302SELECT ts_rank_cd(strip(to_tsvector('both stripped')), 303 to_tsquery('both & stripped')); 304 305SELECT ts_rank_cd(to_tsvector('unstripped') || strip(to_tsvector('stripped')), 306 to_tsquery('unstripped & stripped')); 307 308--headline tests 309SELECT ts_headline('english', ' 310Day after day, day after day, 311 We stuck, nor breath nor motion, 312As idle as a painted Ship 313 Upon a painted Ocean. 314Water, water, every where 315 And all the boards did shrink; 316Water, water, every where, 317 Nor any drop to drink. 318S. T. Coleridge (1772-1834) 319', to_tsquery('english', 'paint&water')); 320 321SELECT ts_headline('english', ' 322Day after day, day after day, 323 We stuck, nor breath nor motion, 324As idle as a painted Ship 325 Upon a painted Ocean. 326Water, water, every where 327 And all the boards did shrink; 328Water, water, every where, 329 Nor any drop to drink. 330S. T. Coleridge (1772-1834) 331', to_tsquery('english', 'breath&motion&water')); 332 333SELECT ts_headline('english', ' 334Day after day, day after day, 335 We stuck, nor breath nor motion, 336As idle as a painted Ship 337 Upon a painted Ocean. 338Water, water, every where 339 And all the boards did shrink; 340Water, water, every where, 341 Nor any drop to drink. 342S. T. Coleridge (1772-1834) 343', to_tsquery('english', 'ocean')); 344 345SELECT ts_headline('english', ' 346Day after day, day after day, 347 We stuck, nor breath nor motion, 348As idle as a painted Ship 349 Upon a painted Ocean. 350Water, water, every where 351 And all the boards did shrink; 352Water, water, every where, 353 Nor any drop to drink. 354S. T. Coleridge (1772-1834) 355', phraseto_tsquery('english', 'painted Ocean')); 356 357SELECT ts_headline('english', ' 358Day after day, day after day, 359 We stuck, nor breath nor motion, 360As idle as a painted Ship 361 Upon a painted Ocean. 362Water, water, every where 363 And all the boards did shrink; 364Water, water, every where, 365 Nor any drop to drink. 366S. T. Coleridge (1772-1834) 367', phraseto_tsquery('english', 'idle as a painted Ship')); 368 369SELECT ts_headline('english', 370'Lorem ipsum urna. Nullam nullam ullamcorper urna.', 371to_tsquery('english','Lorem') && phraseto_tsquery('english','ullamcorper urna'), 372'MaxWords=100, MinWords=1'); 373 374SELECT ts_headline('english', ' 375<html> 376<!-- some comment --> 377<body> 378Sea view wow <u>foo bar</u> <i>qq</i> 379<a href="http://www.google.com/foo.bar.html" target="_blank">YES </a> 380ff-bg 381<script> 382 document.write(15); 383</script> 384</body> 385</html>', 386to_tsquery('english', 'sea&foo'), 'HighlightAll=true'); 387 388SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 <-> 3', 'MaxWords=2, MinWords=1'); 389SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 & 3', 'MaxWords=4, MinWords=1'); 390SELECT ts_headline('simple', '1 2 3 1 3'::text, '1 <-> 3', 'MaxWords=4, MinWords=1'); 391 392--Check if headline fragments work 393SELECT ts_headline('english', ' 394Day after day, day after day, 395 We stuck, nor breath nor motion, 396As idle as a painted Ship 397 Upon a painted Ocean. 398Water, water, every where 399 And all the boards did shrink; 400Water, water, every where, 401 Nor any drop to drink. 402S. T. Coleridge (1772-1834) 403', to_tsquery('english', 'ocean'), 'MaxFragments=1'); 404 405--Check if more than one fragments are displayed 406SELECT ts_headline('english', ' 407Day after day, day after day, 408 We stuck, nor breath nor motion, 409As idle as a painted Ship 410 Upon a painted Ocean. 411Water, water, every where 412 And all the boards did shrink; 413Water, water, every where, 414 Nor any drop to drink. 415S. T. Coleridge (1772-1834) 416', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2'); 417 418--Fragments when there all query words are not in the document 419SELECT ts_headline('english', ' 420Day after day, day after day, 421 We stuck, nor breath nor motion, 422As idle as a painted Ship 423 Upon a painted Ocean. 424Water, water, every where 425 And all the boards did shrink; 426Water, water, every where, 427 Nor any drop to drink. 428S. T. Coleridge (1772-1834) 429', to_tsquery('english', 'ocean & seahorse'), 'MaxFragments=1'); 430 431--FragmentDelimiter option 432SELECT ts_headline('english', ' 433Day after day, day after day, 434 We stuck, nor breath nor motion, 435As idle as a painted Ship 436 Upon a painted Ocean. 437Water, water, every where 438 And all the boards did shrink; 439Water, water, every where, 440 Nor any drop to drink. 441S. T. Coleridge (1772-1834) 442', to_tsquery('english', 'Coleridge & stuck'), 'MaxFragments=2,FragmentDelimiter=***'); 443 444--Fragments with phrase search 445SELECT ts_headline('english', 446'Lorem ipsum urna. Nullam nullam ullamcorper urna.', 447to_tsquery('english','Lorem') && phraseto_tsquery('english','ullamcorper urna'), 448'MaxFragments=100, MaxWords=100, MinWords=1'); 449 450--Rewrite sub system 451 452CREATE TABLE test_tsquery (txtkeyword TEXT, txtsample TEXT); 453\set ECHO none 454\copy test_tsquery from stdin 455'New York' new & york | big & apple | nyc 456Moscow moskva | moscow 457'Sanct Peter' Peterburg | peter | 'Sanct Peterburg' 458'foo bar qq' foo & (bar | qq) & city 4591 & (2 <-> 3) 2 <-> 4 4605 <-> 6 5 <-> 7 461\. 462\set ECHO all 463 464ALTER TABLE test_tsquery ADD COLUMN keyword tsquery; 465UPDATE test_tsquery SET keyword = to_tsquery('english', txtkeyword); 466ALTER TABLE test_tsquery ADD COLUMN sample tsquery; 467UPDATE test_tsquery SET sample = to_tsquery('english', txtsample::text); 468 469 470SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york'; 471SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york'; 472SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york'; 473SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york'; 474SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york'; 475 476CREATE UNIQUE INDEX bt_tsq ON test_tsquery (keyword); 477 478SET enable_seqscan=OFF; 479 480SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york'; 481SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york'; 482SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york'; 483SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york'; 484SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york'; 485 486RESET enable_seqscan; 487 488SELECT ts_rewrite('foo & bar & qq & new & york', 'new & york'::tsquery, 'big & apple | nyc | new & york & city'); 489SELECT ts_rewrite(ts_rewrite('new & !york ', 'york', '!jersey'), 490 'jersey', 'mexico'); 491 492SELECT ts_rewrite('moscow', 'SELECT keyword, sample FROM test_tsquery'::text ); 493SELECT ts_rewrite('moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'::text ); 494SELECT ts_rewrite('bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery'::text ); 495 496SELECT ts_rewrite( 'moscow', 'SELECT keyword, sample FROM test_tsquery'); 497SELECT ts_rewrite( 'moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'); 498SELECT ts_rewrite( 'bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery'); 499 500SELECT ts_rewrite('1 & (2 <-> 3)', 'SELECT keyword, sample FROM test_tsquery'::text ); 501SELECT ts_rewrite('1 & (2 <2> 3)', 'SELECT keyword, sample FROM test_tsquery'::text ); 502SELECT ts_rewrite('5 <-> (1 & (2 <-> 3))', 'SELECT keyword, sample FROM test_tsquery'::text ); 503SELECT ts_rewrite('5 <-> (6 | 8)', 'SELECT keyword, sample FROM test_tsquery'::text ); 504 505-- Check empty substitution 506SELECT ts_rewrite(to_tsquery('5 & (6 | 5)'), to_tsquery('5'), to_tsquery('')); 507SELECT ts_rewrite(to_tsquery('!5'), to_tsquery('5'), to_tsquery('')); 508 509SELECT keyword FROM test_tsquery WHERE keyword @> 'new'; 510SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow'; 511SELECT keyword FROM test_tsquery WHERE keyword <@ 'new'; 512SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow'; 513SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; 514SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; 515SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; 516SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; 517SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; 518SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; 519 520CREATE INDEX qq ON test_tsquery USING gist (keyword tsquery_ops); 521SET enable_seqscan=OFF; 522 523SELECT keyword FROM test_tsquery WHERE keyword @> 'new'; 524SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow'; 525SELECT keyword FROM test_tsquery WHERE keyword <@ 'new'; 526SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow'; 527SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; 528SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; 529SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; 530SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; 531SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; 532SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; 533 534SELECT ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz'); 535SELECT to_tsvector('foo bar') @@ 536 ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz'); 537SELECT to_tsvector('bar baz') @@ 538 ts_rewrite(tsquery_phrase('foo', 'foo'), 'foo', 'bar | baz'); 539 540RESET enable_seqscan; 541 542--test GUC 543SET default_text_search_config=simple; 544 545SELECT to_tsvector('SKIES My booKs'); 546SELECT plainto_tsquery('SKIES My booKs'); 547SELECT to_tsquery('SKIES & My | booKs'); 548 549SET default_text_search_config=english; 550 551SELECT to_tsvector('SKIES My booKs'); 552SELECT plainto_tsquery('SKIES My booKs'); 553SELECT to_tsquery('SKIES & My | booKs'); 554 555--trigger 556CREATE TRIGGER tsvectorupdate 557BEFORE UPDATE OR INSERT ON test_tsvector 558FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(a, 'pg_catalog.english', t); 559 560SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); 561INSERT INTO test_tsvector (t) VALUES ('345 qwerty'); 562SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); 563UPDATE test_tsvector SET t = null WHERE t = '345 qwerty'; 564SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); 565 566INSERT INTO test_tsvector (t) VALUES ('345 qwerty'); 567 568SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); 569 570-- test finding items in GIN's pending list 571create temp table pendtest (ts tsvector); 572create index pendtest_idx on pendtest using gin(ts); 573insert into pendtest values (to_tsvector('Lore ipsam')); 574insert into pendtest values (to_tsvector('Lore ipsum')); 575select * from pendtest where 'ipsu:*'::tsquery @@ ts; 576select * from pendtest where 'ipsa:*'::tsquery @@ ts; 577select * from pendtest where 'ips:*'::tsquery @@ ts; 578select * from pendtest where 'ipt:*'::tsquery @@ ts; 579select * from pendtest where 'ipi:*'::tsquery @@ ts; 580 581--check OP_PHRASE on index 582create temp table phrase_index_test(fts tsvector); 583insert into phrase_index_test values ('A fat cat has just eaten a rat.'); 584insert into phrase_index_test values (to_tsvector('english', 'A fat cat has just eaten a rat.')); 585create index phrase_index_test_idx on phrase_index_test using gin(fts); 586set enable_seqscan = off; 587select * from phrase_index_test where fts @@ phraseto_tsquery('english', 'fat cat'); 588set enable_seqscan = on; 589