tsearch.out

来自「postgresql8.3.4源码,开源数据库」· OUT 代码 · 共 920 行 · 第 1/2 页

--
-- Sanity checks for text search catalogs
--
-- NB: we assume the oidjoins test will have caught any dangling links,
-- that is OID or REGPROC fields that are not zero and do not match some
-- row in the linked-to table.  However, if we want to enforce that a link
-- field can't be 0, we have to check it here.
-- Find unexpected zero link entries
SELECT oid, prsname
FROM pg_ts_parser
WHERE prsnamespace = 0 OR prsstart = 0 OR prstoken = 0 OR prsend = 0 OR
      -- prsheadline is optional
      prslextype = 0;
 oid | prsname 
-----+---------
(0 rows)

SELECT oid, dictname
FROM pg_ts_dict
WHERE dictnamespace = 0 OR dictowner = 0 OR dicttemplate = 0;
 oid | dictname 
-----+----------
(0 rows)

SELECT oid, tmplname
FROM pg_ts_template
WHERE tmplnamespace = 0 OR tmpllexize = 0;  -- tmplinit is optional
 oid | tmplname 
-----+----------
(0 rows)

SELECT oid, cfgname
FROM pg_ts_config
WHERE cfgnamespace = 0 OR cfgowner = 0 OR cfgparser = 0;
 oid | cfgname 
-----+---------
(0 rows)

SELECT mapcfg, maptokentype, mapseqno
FROM pg_ts_config_map
WHERE mapcfg = 0 OR mapdict = 0;
 mapcfg | maptokentype | mapseqno 
--------+--------------+----------
(0 rows)

-- Look for pg_ts_config_map entries that aren't one of parser's token types
SELECT * FROM
  ( SELECT oid AS cfgid, (ts_token_type(cfgparser)).tokid AS tokid
    FROM pg_ts_config ) AS tt 
RIGHT JOIN pg_ts_config_map AS m
    ON (tt.cfgid=m.mapcfg AND tt.tokid=m.maptokentype)
WHERE
    tt.cfgid IS NULL OR tt.tokid IS NULL;
 cfgid | tokid | mapcfg | maptokentype | mapseqno | mapdict 
-------+-------+--------+--------------+----------+---------
(0 rows)

-- test basic text search behavior without indexes, then with
SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
 count 
-------
   158
(1 row)

SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
 count 
-------
    17
(1 row)

SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
 count 
-------
     6
(1 row)

SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
 count 
-------
    98
(1 row)

SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
 count 
-------
    23
(1 row)

SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
 count 
-------
    39
(1 row)

create index wowidx on test_tsvector using gist (a);
SET enable_seqscan=OFF;
SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
 count 
-------
   158
(1 row)

SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
 count 
-------
    17
(1 row)

SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
 count 
-------
     6
(1 row)

SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
 count 
-------
    98
(1 row)

SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
 count 
-------
    23
(1 row)

SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
 count 
-------
    39
(1 row)

RESET enable_seqscan;
DROP INDEX wowidx;
CREATE INDEX wowidx ON test_tsvector USING gin (a);
SET enable_seqscan=OFF;
SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh';
 count 
-------
   158
(1 row)

SELECT count(*) FROM test_tsvector WHERE a @@ 'wr&qh';
 count 
-------
    17
(1 row)

SELECT count(*) FROM test_tsvector WHERE a @@ 'eq&yt';
 count 
-------
     6
(1 row)

SELECT count(*) FROM test_tsvector WHERE a @@ 'eq|yt';
 count 
-------
    98
(1 row)

SELECT count(*) FROM test_tsvector WHERE a @@ '(eq&yt)|(wr&qh)';
 count 
-------
    23
(1 row)

SELECT count(*) FROM test_tsvector WHERE a @@ '(eq|yt)&(wr|qh)';
 count 
-------
    39
(1 row)

  
RESET enable_seqscan;
INSERT INTO test_tsvector VALUES ('???', 'DFG:1A,2B,6C,10 FGH');
SELECT * FROM ts_stat('SELECT a FROM test_tsvector') ORDER BY ndoc DESC, nentry DESC, word LIMIT 10;
 word | ndoc | nentry 
------+------+--------
 qq   |  108 |    108
 qt   |  102 |    102
 qe   |  100 |    100
 qh   |   98 |     98
 qw   |   98 |     98
 qa   |   97 |     97
 ql   |   94 |     94
 qs   |   94 |     94
 qi   |   92 |     92
 qr   |   92 |     92
(10 rows)

SELECT * FROM ts_stat('SELECT a FROM test_tsvector', 'AB') ORDER BY ndoc DESC, nentry DESC, word;
 word | ndoc | nentry 
------+------+--------
 DFG  |    1 |      2
(1 row)

--dictionaries and to_tsvector
SELECT ts_lexize('english_stem', 'skies');
 ts_lexize 
-----------
 {sky}
(1 row)

SELECT ts_lexize('english_stem', 'identity');
 ts_lexize 
-----------
 {ident}
(1 row)

SELECT * FROM ts_token_type('default');
 tokid |      alias      |               description                
-------+-----------------+------------------------------------------
     1 | asciiword       | Word, all ASCII
     2 | word            | Word, all letters
     3 | numword         | Word, letters and digits
     4 | email           | Email address
     5 | url             | URL
     6 | host            | Host
     7 | sfloat          | Scientific notation
     8 | version         | Version number
     9 | hword_numpart   | Hyphenated word part, letters and digits
    10 | hword_part      | Hyphenated word part, all letters
    11 | hword_asciipart | Hyphenated word part, all ASCII
    12 | blank           | Space symbols
    13 | tag             | XML tag
    14 | protocol        | Protocol head
    15 | numhword        | Hyphenated word, letters and digits
    16 | asciihword      | Hyphenated word, all ASCII
    17 | hword           | Hyphenated word, all letters
    18 | url_path        | URL path
    19 | file            | File or path name
    20 | float           | Decimal notation
    21 | int             | Signed integer
    22 | uint            | Unsigned integer
    23 | entity          | XML entity
(23 rows)

SELECT * 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 [e-mail] qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
/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
<i <b> wow  < jqw <> qwerty');
 tokid |                token                 
-------+--------------------------------------
    22 | 345
    12 |  
     1 | qwe
    12 | @
    19 | efd.r
    12 |  ' 
    14 | http://
     6 | www.com
    12 | / 
    14 | http://
     5 | aew.werc.ewr/?ad=qwe&dw
     6 | aew.werc.ewr
    18 | /?ad=qwe&dw
    12 |  
     5 | 1aew.werc.ewr/?ad=qwe&dw
     6 | 1aew.werc.ewr
    18 | /?ad=qwe&dw
    12 |  
     6 | 2aew.werc.ewr
    12 |  
    14 | http://
     5 | 3aew.werc.ewr/?ad=qwe&dw
     6 | 3aew.werc.ewr
    18 | /?ad=qwe&dw
    12 |  
    14 | http://
     6 | 4aew.werc.ewr
    12 |  
    14 | http://
     6 | 5aew.werc.ewr:8100
    12 | /?  
     1 | ad
    12 | =
     1 | qwe
    12 | &
     1 | dw
    12 |  
     5 | 6aew.werc.ewr:8100/?ad=qwe&dw
     6 | 6aew.werc.ewr:8100
    18 | /?ad=qwe&dw
    12 |  
     5 | 7aew.werc.ewr:8100/?ad=qwe&dw=%20%32
     6 | 7aew.werc.ewr:8100
    18 | /?ad=qwe&dw=%20%32
    12 |  
     7 | +4.0e-10
    12 |  
     1 | qwe
    12 |  
     1 | qwe
    12 |  
     1 | qwqwe
    12 |  
    20 | 234.435
    12 |  
    22 | 455
    12 |  
    20 | 5.005
    12 |  
     4 | [e-mail]
    12 |  
    16 | qwe-wer
    11 | qwe
    12 | -
    11 | wer
    12 |  
     1 | asdf
    12 |  
    13 | <fr>
     1 | qwer
    12 |  
     1 | jf
    12 |  
     1 | sdjk
    12 | <
     1 | we
    12 |  
     1 | hjwer
    12 |  
    13 | <werrwe>
    12 |  
     3 | ewr1
    12 | > 
     3 | ewri2
    12 |  
    13 | <a href="qwe<qwe>">
    12 | 
       : 
    19 | /usr/local/fff
    12 |  
    19 | /awdf/dwqe/4325
    12 |  
    19 | rewt/ewr
    12 |  
     1 | wefjn
    12 |  
    19 | /wqe-324/ewr
    12 |  
    19 | gist.h
    12 |  
    19 | gist.h.c
    12 |  
    19 | gist.c
    12 | . 
     1 | readline
    12 |  
    20 | 4.2
    12 |  
    20 | 4.2
    12 | . 
    20 | 4.2
    12 | , 
     1 | readline
    20 | -4.2
    12 |  
     1 | readline
    20 | -4.2
    12 | . 
    22 | 234
    12 | 
       : 
    12 | <
     1 | i
    12 |  
    13 | <b>
    12 |  
     1 | wow
    12 |   
    12 | < 
     1 | jqw
    12 |  
    12 | <> 
     1 | qwerty
(131 rows)

SELECT 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 [e-mail] qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
/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
<i <b> wow  < jqw <> qwerty');
                                                                                                                                                                                                                                                                                                                                                                                                                      to_tsvector                                                                                                                                                                                                                                                                                                                                                                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 'ad':17 'dw':19 'jf':39 '234':61 '345':1 '4.2':54,55,56 '455':31 'jqw':64 'qwe':2,18,27,28,35 'wer':36 'wow':63 '-4.2':58,60 'asdf':37 'ewr1':43 'qwer':38 'sdjk':40 '5.005':32 'efd.r':3 'ewri2':44 'hjwer':42 'qwqwe':29 'wefjn':48 'gist.c':52 'gist.h':50 'qwerti':65 '234.435':30 'qwe-wer':34 'readlin':53,57,59 'www.com':4 '+4.0e-10':26 'gist.h.c':51 'rewt/ewr':47 '/?ad=qwe&dw':7,10,14,22 '/wqe-324/ewr':49 'aew.werc.ewr':6 '1aew.werc.ewr':9 '2aew.werc.ewr':11 '3aew.werc.ewr':13 '4aew.werc.ewr':15 '/usr/local/fff':45 '/awdf/dwqe/4325':46 '[e-mail]':33 '/?ad=qwe&dw=%20%32':25 '5aew.werc.ewr:8100':16 '6aew.werc.ewr:8100':21 '7aew.werc.ewr:8100':24 'aew.werc.ewr/?ad=qwe&dw':5 '1aew.werc.ewr/?ad=qwe&dw':8 '3aew.werc.ewr/?ad=qwe&dw':12 '6aew.werc.ewr:8100/?ad=qwe&dw':20 '7aew.werc.ewr:8100/?ad=qwe&dw=%20%32':23
(1 row)

SELECT 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 [e-mail] qwe-wer asdf <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
/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
<i <b> wow  < jqw <> qwerty'));
 length 
--------
     51
(1 row)

-- ts_debug
SELECT * from ts_debug('english', '<myns:foo-bar_baz.blurfl>abc&nm1;def&#xa9;ghi&#245;jkl</myns:foo-bar_baz.blurfl>');
   alias   |   description   |           token            |  dictionaries  |  dictionary  | lexemes 
-----------+-----------------+----------------------------+----------------+--------------+---------
 tag       | XML tag         | <myns:foo-bar_baz.blurfl>  | {}             |              | 
 asciiword | Word, all ASCII | abc                        | {english_stem} | english_stem | {abc}
 entity    | XML entity      | &nm1;                      | {}             |              | 
 asciiword | Word, all ASCII | def                        | {english_stem} | english_stem | {def}
 entity    | XML entity      | &#xa9;                     | {}             |              | 
 asciiword | Word, all ASCII | ghi                        | {english_stem} | english_stem | {ghi}
 entity    | XML entity      | &#245;                     | {}             |              | 
 asciiword | Word, all ASCII | jkl                        | {english_stem} | english_stem | {jkl}
 tag       | XML tag         | </myns:foo-bar_baz.blurfl> | {}             |              | 
(9 rows)

-- to_tsquery
SELECT to_tsquery('english', 'qwe & sKies ');
  to_tsquery   
---------------
 'qwe' & 'sky'
(1 row)

SELECT to_tsquery('simple', 'qwe & sKies ');
   to_tsquery    
-----------------
 'qwe' & 'skies'
(1 row)

SELECT to_tsquery('english', '''the wether'':dc & ''           sKies '':BC ');
       to_tsquery       
------------------------
 'wether':CD & 'sky':BC
(1 row)

SELECT to_tsquery('english', 'asd&(and|fghj)');
   to_tsquery   
----------------
 'asd' & 'fghj'
(1 row)

SELECT to_tsquery('english', '(asd&and)|fghj');
   to_tsquery   
----------------
 'asd' | 'fghj'
(1 row)

SELECT to_tsquery('english', '(asd&!and)|fghj');
   to_tsquery   
----------------
 'asd' | 'fghj'
(1 row)

SELECT to_tsquery('english', '(the|and&(i&1))&fghj');
  to_tsquery  
--------------
 '1' & 'fghj'
(1 row)

SELECT plainto_tsquery('english', 'the and z 1))& fghj');
  plainto_tsquery   
--------------------
 'z' & '1' & 'fghj'
(1 row)

SELECT plainto_tsquery('english', 'foo bar') && plainto_tsquery('english', 'asd');
       ?column?        
-----------------------