tsearch.out
来自「postgresql8.3.4源码,开源数据库」· OUT 代码 · 共 920 行 · 第 1/2 页
OUT
920 行
'foo' & 'bar' & 'asd'(1 row)SELECT plainto_tsquery('english', 'foo bar') || plainto_tsquery('english', 'asd fg'); ?column? ------------------------------ 'foo' & 'bar' | 'asd' & 'fg'(1 row)SELECT plainto_tsquery('english', 'foo bar') || !!plainto_tsquery('english', 'asd fg'); ?column? ----------------------------------- 'foo' & 'bar' | !( 'asd' & 'fg' )(1 row)SELECT plainto_tsquery('english', 'foo bar') && 'asd | fg'; ?column? ---------------------------------- 'foo' & 'bar' & ( 'asd' | 'fg' )(1 row)SELECT ts_rank_cd(to_tsvector('english', 'Day after day, day after day, We stuck, nor breath nor motion,As idle as a painted Ship Upon a painted Ocean.Water, water, every where And all the boards did shrink;Water, water, every where, Nor any drop to drink.S. T. Coleridge (1772-1834)'), to_tsquery('english', 'paint&water')); ts_rank_cd ------------ 0.05(1 row)SELECT ts_rank_cd(to_tsvector('english', 'Day after day, day after day, We stuck, nor breath nor motion,As idle as a painted Ship Upon a painted Ocean.Water, water, every where And all the boards did shrink;Water, water, every where, Nor any drop to drink.S. T. Coleridge (1772-1834)'), to_tsquery('english', 'breath&motion&water')); ts_rank_cd ------------ 0.00833333(1 row)SELECT ts_rank_cd(to_tsvector('english', 'Day after day, day after day, We stuck, nor breath nor motion,As idle as a painted Ship Upon a painted Ocean.Water, water, every where And all the boards did shrink;Water, water, every where, Nor any drop to drink.S. T. Coleridge (1772-1834)'), to_tsquery('english', 'ocean')); ts_rank_cd ------------ 0.1(1 row)--headline testsSELECT ts_headline('english', 'Day after day, day after day, We stuck, nor breath nor motion,As idle as a painted Ship Upon a painted Ocean.Water, water, every where And all the boards did shrink;Water, water, every where, Nor any drop to drink.S. T. Coleridge (1772-1834)', to_tsquery('english', 'paint&water')); ts_headline ----------------------------------------- <b>painted</b> Ocean. <b>Water</b>, <b>water</b>, every where And all the boards did shrink; <b>Water</b>, <b>water</b>, every(1 row)SELECT ts_headline('english', 'Day after day, day after day, We stuck, nor breath nor motion,As idle as a painted Ship Upon a painted Ocean.Water, water, every where And all the boards did shrink;Water, water, every where, Nor any drop to drink.S. T. Coleridge (1772-1834)', to_tsquery('english', 'breath&motion&water')); ts_headline ---------------------------------- <b>breath</b> nor <b>motion</b>, As idle as a painted Ship Upon a painted Ocean. <b>Water</b>, <b>water</b>(1 row)SELECT ts_headline('english', 'Day after day, day after day, We stuck, nor breath nor motion,As idle as a painted Ship Upon a painted Ocean.Water, water, every where And all the boards did shrink;Water, water, every where, Nor any drop to drink.S. T. Coleridge (1772-1834)', to_tsquery('english', 'ocean')); ts_headline ---------------------------------- <b>Ocean</b>. Water, water, every where And all the boards did shrink; Water, water, every where(1 row)SELECT ts_headline('english', '<html><!-- some comment --><body>Sea view wow <u>foo bar</u> <i>qq</i><a href="http://www.google.com/foo.bar.html" target="_blank">YES </a>ff-bg<script> document.write(15);</script></body></html>',to_tsquery('english', 'sea&foo'), 'HighlightAll=true'); ts_headline ----------------------------------------------------------------------------- <html> <!-- some comment --> <body> <b>Sea</b> view wow <u><b>foo</b> bar</u> <i>qq</i> <a href="http://www.google.com/foo.bar.html" target="_blank">YES </a> ff-bg <script> document.write(15); </script> </body> </html>(1 row)--Rewrite sub systemCREATE TABLE test_tsquery (txtkeyword TEXT, txtsample TEXT);\set ECHO noneALTER TABLE test_tsquery ADD COLUMN keyword tsquery;UPDATE test_tsquery SET keyword = to_tsquery('english', txtkeyword);ALTER TABLE test_tsquery ADD COLUMN sample tsquery;UPDATE test_tsquery SET sample = to_tsquery('english', txtsample::text);SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york'; count ------- 1(1 row)SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york'; count ------- 2(1 row)SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york'; count ------- 1(1 row)SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york'; count ------- 3(1 row)SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york'; count ------- 2(1 row)CREATE UNIQUE INDEX bt_tsq ON test_tsquery (keyword);SET enable_seqscan=OFF;SELECT COUNT(*) FROM test_tsquery WHERE keyword < 'new & york'; count ------- 1(1 row)SELECT COUNT(*) FROM test_tsquery WHERE keyword <= 'new & york'; count ------- 2(1 row)SELECT COUNT(*) FROM test_tsquery WHERE keyword = 'new & york'; count ------- 1(1 row)SELECT COUNT(*) FROM test_tsquery WHERE keyword >= 'new & york'; count ------- 3(1 row)SELECT COUNT(*) FROM test_tsquery WHERE keyword > 'new & york'; count ------- 2(1 row)RESET enable_seqscan;SELECT ts_rewrite('foo & bar & qq & new & york', 'new & york'::tsquery, 'big & apple | nyc | new & york & city'); ts_rewrite ---------------------------------------------------------------------------------- 'foo' & 'bar' & 'qq' & ( 'city' & 'new' & 'york' | ( 'nyc' | 'big' & 'apple' ) )(1 row)SELECT ts_rewrite('moscow', 'SELECT keyword, sample FROM test_tsquery'::text ); ts_rewrite --------------------- 'moskva' | 'moscow'(1 row)SELECT ts_rewrite('moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'::text ); ts_rewrite ----------------------------------- 'hotel' & ( 'moskva' | 'moscow' )(1 row)SELECT ts_rewrite('bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery'::text ); ts_rewrite ------------------------------------------------------------------------------------- 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )(1 row)SELECT ts_rewrite( 'moscow', 'SELECT keyword, sample FROM test_tsquery'); ts_rewrite --------------------- 'moskva' | 'moscow'(1 row)SELECT ts_rewrite( 'moscow & hotel', 'SELECT keyword, sample FROM test_tsquery'); ts_rewrite ----------------------------------- 'hotel' & ( 'moskva' | 'moscow' )(1 row)SELECT ts_rewrite( 'bar & new & qq & foo & york', 'SELECT keyword, sample FROM test_tsquery'); ts_rewrite ------------------------------------------------------------------------------------- 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )(1 row)SELECT keyword FROM test_tsquery WHERE keyword @> 'new'; keyword ---------------- 'new' & 'york'(1 row)SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow'; keyword ---------- 'moscow'(1 row)SELECT keyword FROM test_tsquery WHERE keyword <@ 'new'; keyword ---------(0 rows)SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow'; keyword ---------- 'moscow'(1 row)SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; ts_rewrite --------------------- 'moskva' | 'moscow'(1 row)SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; ts_rewrite ----------------------------------- 'hotel' & ( 'moskva' | 'moscow' )(1 row)SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; ts_rewrite ------------------------------------------------------------------------------------- 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )(1 row)SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; ts_rewrite --------------------- 'moskva' | 'moscow'(1 row)SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; ts_rewrite ----------------------------------- 'hotel' & ( 'moskva' | 'moscow' )(1 row)SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; ts_rewrite ------------------------------------------------------------------------------------- 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )(1 row)CREATE INDEX qq ON test_tsquery USING gist (keyword tsquery_ops);SET enable_seqscan=OFF;SELECT keyword FROM test_tsquery WHERE keyword @> 'new'; keyword ---------------- 'new' & 'york'(1 row)SELECT keyword FROM test_tsquery WHERE keyword @> 'moscow'; keyword ---------- 'moscow'(1 row)SELECT keyword FROM test_tsquery WHERE keyword <@ 'new'; keyword ---------(0 rows)SELECT keyword FROM test_tsquery WHERE keyword <@ 'moscow'; keyword ---------- 'moscow'(1 row)SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; ts_rewrite --------------------- 'moskva' | 'moscow'(1 row)SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; ts_rewrite ----------------------------------- 'hotel' & ( 'moskva' | 'moscow' )(1 row)SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; ts_rewrite ------------------------------------------------------------------------------------- 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )(1 row)SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow') AS query; ts_rewrite --------------------- 'moskva' | 'moscow'(1 row)SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'moscow & hotel') AS query; ts_rewrite ----------------------------------- 'hotel' & ( 'moskva' | 'moscow' )(1 row)SELECT ts_rewrite( query, 'SELECT keyword, sample FROM test_tsquery' ) FROM to_tsquery('english', 'bar & new & qq & foo & york') AS query; ts_rewrite ------------------------------------------------------------------------------------- 'citi' & 'foo' & ( 'bar' | 'qq' ) & ( 'nyc' | ( 'big' & 'appl' | 'new' & 'york' ) )(1 row)RESET enable_seqscan;--test GUCSET default_text_search_config=simple;SELECT to_tsvector('SKIES My booKs'); to_tsvector ---------------------------- 'my':2 'books':3 'skies':1(1 row)SELECT plainto_tsquery('SKIES My booKs'); plainto_tsquery -------------------------- 'skies' & 'my' & 'books'(1 row)SELECT to_tsquery('SKIES & My | booKs'); to_tsquery -------------------------- 'skies' & 'my' | 'books'(1 row)SET default_text_search_config=english;SELECT to_tsvector('SKIES My booKs'); to_tsvector ------------------ 'sky':1 'book':3(1 row)SELECT plainto_tsquery('SKIES My booKs'); plainto_tsquery ----------------- 'sky' & 'book'(1 row)SELECT to_tsquery('SKIES & My | booKs'); to_tsquery ---------------- 'sky' | 'book'(1 row)--triggerCREATE TRIGGER tsvectorupdateBEFORE UPDATE OR INSERT ON test_tsvectorFOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(a, 'pg_catalog.english', t);SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); count ------- 0(1 row)INSERT INTO test_tsvector (t) VALUES ('345 qwerty');SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); count ------- 1(1 row)UPDATE test_tsvector SET t = null WHERE t = '345 qwerty';SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); count ------- 0(1 row)INSERT INTO test_tsvector (t) VALUES ('345 qwerty');SELECT count(*) FROM test_tsvector WHERE a @@ to_tsquery('345&qwerty'); count ------- 1(1 row)⌨️ 快捷键说明
复制代码Ctrl + C
搜索代码Ctrl + F
全屏模式F11
增大字号Ctrl + =
减小字号Ctrl + -
显示快捷键?