txtidx datatype for full text indexing with GiST.

From Oleg Bartunov and Teodor Sigaev.

1 files changed, 739 insertions, 0 deletions

diff --git a/contrib/tsearch/expected/tsearch.out b/contrib/tsearch/expected/tsearch.out
new file mode 100644
index 0000000000..d0533b73c0
--- /dev/null
+++ b/contrib/tsearch/expected/tsearch.out
@@ -0,0 +1,739 @@
+--
+-- first, define the datatype.  Turn off echoing so that expected file
+-- does not depend on contents of seg.sql.
+--
+\set ECHO none
+--txtidx
+select '1'::txtidx;
+ txtidx 
+--------
+ '1'
+(1 row)
+
+select '1 '::txtidx;
+ txtidx 
+--------
+ '1'
+(1 row)
+
+select ' 1'::txtidx;
+ txtidx 
+--------
+ '1'
+(1 row)
+
+select ' 1 '::txtidx;
+ txtidx 
+--------
+ '1'
+(1 row)
+
+select '1 2'::txtidx;
+ txtidx  
+---------
+ '1' '2'
+(1 row)
+
+select '\'1 2\''::txtidx;
+ txtidx 
+--------
+ '1 2'
+(1 row)
+
+select '\'1 \\\'2\''::txtidx;
+ txtidx  
+---------
+ '1 \'2'
+(1 row)
+
+select '\'1 \\\'2\'3'::txtidx;
+   txtidx    
+-------------
+ '3' '1 \'2'
+(1 row)
+
+select '\'1 \\\'2\' 3'::txtidx;
+   txtidx    
+-------------
+ '3' '1 \'2'
+(1 row)
+
+select '\'1 \\\'2\' \' 3\' 4 '::txtidx;
+      txtidx      
+------------------
+ '4' ' 3' '1 \'2'
+(1 row)
+
+--query_txt
+select '1'::query_txt;
+ query_txt 
+-----------
+ '1'
+(1 row)
+
+select '1 '::query_txt;
+ query_txt 
+-----------
+ '1'
+(1 row)
+
+select ' 1'::query_txt;
+ query_txt 
+-----------
+ '1'
+(1 row)
+
+select ' 1 '::query_txt;
+ query_txt 
+-----------
+ '1'
+(1 row)
+
+select '\'1 2\''::query_txt;
+ query_txt 
+-----------
+ '1 2'
+(1 row)
+
+select '\'1 \\\'2\''::query_txt;
+ query_txt 
+-----------
+ '1 \'2'
+(1 row)
+
+select '!1'::query_txt;
+ query_txt 
+-----------
+ !'1'
+(1 row)
+
+select '1|2'::query_txt;
+ query_txt 
+-----------
+ '1' | '2'
+(1 row)
+
+select '1|!2'::query_txt;
+ query_txt  
+------------
+ '1' | !'2'
+(1 row)
+
+select '!1|2'::query_txt;
+ query_txt  
+------------
+ !'1' | '2'
+(1 row)
+
+select '!1|!2'::query_txt;
+  query_txt  
+-------------
+ !'1' | !'2'
+(1 row)
+
+select '!(!1|!2)'::query_txt;
+    query_txt     
+------------------
+ !( !'1' | !'2' )
+(1 row)
+
+select '!(!1|2)'::query_txt;
+    query_txt    
+-----------------
+ !( !'1' | '2' )
+(1 row)
+
+select '!(1|!2)'::query_txt;
+    query_txt    
+-----------------
+ !( '1' | !'2' )
+(1 row)
+
+select '!(1|2)'::query_txt;
+   query_txt    
+----------------
+ !( '1' | '2' )
+(1 row)
+
+select '1&2'::query_txt;
+ query_txt 
+-----------
+ '1' & '2'
+(1 row)
+
+select '!1&2'::query_txt;
+ query_txt  
+------------
+ !'1' & '2'
+(1 row)
+
+select '1&!2'::query_txt;
+ query_txt  
+------------
+ '1' & !'2'
+(1 row)
+
+select '!1&!2'::query_txt;
+  query_txt  
+-------------
+ !'1' & !'2'
+(1 row)
+
+select '(1&2)'::query_txt;
+ query_txt 
+-----------
+ '1' & '2'
+(1 row)
+
+select '1&(2)'::query_txt;
+ query_txt 
+-----------
+ '1' & '2'
+(1 row)
+
+select '!(1)&2'::query_txt;
+ query_txt  
+------------
+ !'1' & '2'
+(1 row)
+
+select '!(1&2)'::query_txt;
+   query_txt    
+----------------
+ !( '1' & '2' )
+(1 row)
+
+select '1|2&3'::query_txt;
+    query_txt    
+-----------------
+ '1' | '2' & '3'
+(1 row)
+
+select '1|(2&3)'::query_txt;
+    query_txt    
+-----------------
+ '1' | '2' & '3'
+(1 row)
+
+select '(1|2)&3'::query_txt;
+      query_txt      
+---------------------
+ ( '1' | '2' ) & '3'
+(1 row)
+
+select '1|2&!3'::query_txt;
+    query_txt     
+------------------
+ '1' | '2' & !'3'
+(1 row)
+
+select '1|!2&3'::query_txt;
+    query_txt     
+------------------
+ '1' | !'2' & '3'
+(1 row)
+
+select '!1|2&3'::query_txt;
+    query_txt     
+------------------
+ !'1' | '2' & '3'
+(1 row)
+
+select '!1|(2&3)'::query_txt;
+    query_txt     
+------------------
+ !'1' | '2' & '3'
+(1 row)
+
+select '!(1|2)&3'::query_txt;
+      query_txt       
+----------------------
+ !( '1' | '2' ) & '3'
+(1 row)
+
+select '(!1|2)&3'::query_txt;
+      query_txt       
+----------------------
+ ( !'1' | '2' ) & '3'
+(1 row)
+
+select '1|(2|(4|(5|6)))'::query_txt;
+                query_txt                
+-----------------------------------------
+ '1' | ( '2' | ( '4' | ( '5' | '6' ) ) )
+(1 row)
+
+select '1|2|4|5|6'::query_txt;
+                query_txt                
+-----------------------------------------
+ ( ( ( '1' | '2' ) | '4' ) | '5' ) | '6'
+(1 row)
+
+select '1&(2&(4&(5&6)))'::query_txt;
+          query_txt          
+-----------------------------
+ '1' & '2' & '4' & '5' & '6'
+(1 row)
+
+select '1&2&4&5&6'::query_txt;
+          query_txt          
+-----------------------------
+ '1' & '2' & '4' & '5' & '6'
+(1 row)
+
+select '1&(2&(4&(5|6)))'::query_txt;
+            query_txt            
+---------------------------------
+ '1' & '2' & '4' & ( '5' | '6' )
+(1 row)
+
+select '1&(2&(4&(5|!6)))'::query_txt;
+            query_txt             
+----------------------------------
+ '1' & '2' & '4' & ( '5' | !'6' )
+(1 row)
+
+select '1&(\'2\'&(\' 4\'&(\\|5 | \'6 \\\' !|&\')))'::query_txt;
+                query_txt                 
+------------------------------------------
+ '1' & '2' & ' 4' & ( '|5' | '6 \' !|&' )
+(1 row)
+
+select '1'::mquery_txt;
+ mquery_txt 
+------------
+ '1'
+(1 row)
+
+select '1 '::mquery_txt;
+ mquery_txt 
+------------
+ '1'
+(1 row)
+
+select ' 1'::mquery_txt;
+ mquery_txt 
+------------
+ '1'
+(1 row)
+
+select ' 1 '::mquery_txt;
+ mquery_txt 
+------------
+ '1'
+(1 row)
+
+select '\'1 2\''::mquery_txt;
+ mquery_txt 
+------------
+ '1' & '2'
+(1 row)
+
+select '\'1 \\\'2\''::mquery_txt;
+ mquery_txt 
+------------
+ '1' & '2'
+(1 row)
+
+select '!1'::mquery_txt;
+ mquery_txt 
+------------
+ !'1'
+(1 row)
+
+select '1|2'::mquery_txt;
+ mquery_txt 
+------------
+ '1' | '2'
+(1 row)
+
+select '1|!2'::mquery_txt;
+ mquery_txt 
+------------
+ '1' | !'2'
+(1 row)
+
+select '!1|2'::mquery_txt;
+ mquery_txt 
+------------
+ !'1' | '2'
+(1 row)
+
+select '!1|!2'::mquery_txt;
+ mquery_txt  
+-------------
+ !'1' | !'2'
+(1 row)
+
+select '!(!1|!2)'::mquery_txt;
+    mquery_txt    
+------------------
+ !( !'1' | !'2' )
+(1 row)
+
+select '!(!1|2)'::mquery_txt;
+   mquery_txt    
+-----------------
+ !( !'1' | '2' )
+(1 row)
+
+select '!(1|!2)'::mquery_txt;
+   mquery_txt    
+-----------------
+ !( '1' | !'2' )
+(1 row)
+
+select '!(1|2)'::mquery_txt;
+   mquery_txt   
+----------------
+ !( '1' | '2' )
+(1 row)
+
+select '1&2'::mquery_txt;
+ mquery_txt 
+------------
+ '1' & '2'
+(1 row)
+
+select '!1&2'::mquery_txt;
+ mquery_txt 
+------------
+ !'1' & '2'
+(1 row)
+
+select '1&!2'::mquery_txt;
+ mquery_txt 
+------------
+ '1' & !'2'
+(1 row)
+
+select '!1&!2'::mquery_txt;
+ mquery_txt  
+-------------
+ !'1' & !'2'
+(1 row)
+
+select '(1&2)'::mquery_txt;
+ mquery_txt 
+------------
+ '1' & '2'
+(1 row)
+
+select '1&(2)'::mquery_txt;
+ mquery_txt 
+------------
+ '1' & '2'
+(1 row)
+
+select '!(1)&2'::mquery_txt;
+ mquery_txt 
+------------
+ !'1' & '2'
+(1 row)
+
+select '!(1&2)'::mquery_txt;
+   mquery_txt   
+----------------
+ !( '1' & '2' )
+(1 row)
+
+select '1|2&3'::mquery_txt;
+   mquery_txt    
+-----------------
+ '1' | '2' & '3'
+(1 row)
+
+select '1|(2&3)'::mquery_txt;
+   mquery_txt    
+-----------------
+ '1' | '2' & '3'
+(1 row)
+
+select '(1|2)&3'::mquery_txt;
+     mquery_txt      
+---------------------
+ ( '1' | '2' ) & '3'
+(1 row)
+
+select '1|2&!3'::mquery_txt;
+    mquery_txt    
+------------------
+ '1' | '2' & !'3'
+(1 row)
+
+select '1|!2&3'::mquery_txt;
+    mquery_txt    
+------------------
+ '1' | !'2' & '3'
+(1 row)
+
+select '!1|2&3'::mquery_txt;
+    mquery_txt    
+------------------
+ !'1' | '2' & '3'
+(1 row)
+
+select '!1|(2&3)'::mquery_txt;
+    mquery_txt    
+------------------
+ !'1' | '2' & '3'
+(1 row)
+
+select '!(1|2)&3'::mquery_txt;
+      mquery_txt      
+----------------------
+ !( '1' | '2' ) & '3'
+(1 row)
+
+select '(!1|2)&3'::mquery_txt;
+      mquery_txt      
+----------------------
+ ( !'1' | '2' ) & '3'
+(1 row)
+
+select '1|(2|(4|(5|6)))'::mquery_txt;
+               mquery_txt                
+-----------------------------------------
+ '1' | ( '2' | ( '4' | ( '5' | '6' ) ) )
+(1 row)
+
+select '1|2|4|5|6'::mquery_txt;
+               mquery_txt                
+-----------------------------------------
+ ( ( ( '1' | '2' ) | '4' ) | '5' ) | '6'
+(1 row)
+
+select '1&(2&(4&(5&6)))'::mquery_txt;
+         mquery_txt          
+-----------------------------
+ '1' & '2' & '4' & '5' & '6'
+(1 row)
+
+select '1&2&4&5&6'::mquery_txt;
+         mquery_txt          
+-----------------------------
+ '1' & '2' & '4' & '5' & '6'
+(1 row)
+
+select '1&(2&(4&(5|6)))'::mquery_txt;
+           mquery_txt            
+---------------------------------
+ '1' & '2' & '4' & ( '5' | '6' )
+(1 row)
+
+select '1&(2&(4&(5|!6)))'::mquery_txt;
+            mquery_txt            
+----------------------------------
+ '1' & '2' & '4' & ( '5' | !'6' )
+(1 row)
+
+select '1&(\'2\'&(\' 4\'&(\\|5 | \'6 \\\' !|&\')))'::mquery_txt;
+           mquery_txt            
+---------------------------------
+ '1' & '2' & '4' & ( '5' | '6' )
+(1 row)
+
+select 'querty-fgries | http://www.google.com/index.html | www.rambler.ru/index.shtml'::mquery_txt;
+                                                                                mquery_txt                                                                                 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ ( 'querty-fgri' & 'querti' & 'fgri' | 'www.google.com/index.html' & 'www.google.com' & '/index.html' ) | 'www.rambler.ru/index.shtml' & 'www.rambler.ru' & '/index.shtml'
+(1 row)
+
+CREATE TABLE test_txtidx( t text, a txtidx );
+\copy test_txtidx from 'data/test_tsearch.data'
+SELECT count(*) FROM test_txtidx WHERE a @@ 'wr|qh';
+ count 
+-------
+    80
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a @@ 'wr&qh';
+ count 
+-------
+     6
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a @@ 'eq&yt';
+ count 
+-------
+     1
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a @@ 'eq|yt';
+ count 
+-------
+    47
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a @@ '(eq&yt)|(wr&qh)';
+ count 
+-------
+     7
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a @@ '(eq|yt)&(wr|qh)';
+ count 
+-------
+    11
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a ## 'wR|qh';
+ count 
+-------
+    80
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a ## 'wR&qh';
+ count 
+-------
+     6
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a ## 'eq&yt';
+ count 
+-------
+     1
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a ## 'eq|yt';
+ count 
+-------
+    47
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a ## '(eq&yt)|(wR&qh)';
+ count 
+-------
+     7
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a ## '(eq|yt)&(wR|qh)';
+ count 
+-------
+    11
+(1 row)
+
+create index wowidx on test_txtidx using gist (a);
+SELECT count(*) FROM test_txtidx WHERE a @@ 'wr|qh';
+ count 
+-------
+    80
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a @@ 'wr&qh';
+ count 
+-------
+     6
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a @@ 'eq&yt';
+ count 
+-------
+     1
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a @@ 'eq|yt';
+ count 
+-------
+    47
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a @@ '(eq&yt)|(wr&qh)';
+ count 
+-------
+     7
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a @@ '(eq|yt)&(wr|qh)';
+ count 
+-------
+    11
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a ## 'wR|qh';
+ count 
+-------
+    80
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a ## 'wR&qh';
+ count 
+-------
+     6
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a ## 'eq&yt';
+ count 
+-------
+     1
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a ## 'eq|yt';
+ count 
+-------
+    47
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a ## '(eq&yt)|(wR&qh)';
+ count 
+-------
+     7
+(1 row)
+
+SELECT count(*) FROM test_txtidx WHERE a ## '(eq|yt)&(wR|qh)';
+ count 
+-------
+    11
+(1 row)
+
+select txt2txtidx('Ë5ÅÎ45 æñùù×ÆÙ× ÙÃÕ 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 weÊ ÊÃÕËÊÃÕËÀÊÃÕËÀË.ÃÆÕ 234.435 455 5.005 teodor@stack.net qwe-wer ÆÙ×-ÐÒ asdf-ÆÙ× asdÆÙ× ÕÒÁ-238 1-ÙÊ <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
+/usr/local/fff /awdf/dwqe/4325 rewt/ewr ×ÕÆÅ/234ac 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');
+                                                                                                                                                                                                                                                                                                                                                                                                                  txt2txtidx                                                                                                                                                                                                                                                                                                                                                                                                                  
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ '1' 'ad' 'dw' 'jf' 'ÐÒ' 'ÙÊ' '234' '238' '345' '4.2' '455' 'jqw' 'qwe' 'wer' 'weÊ' 'wow' 'ÃÆÕ' 'ÆÙ×' 'ÕÒÁ' 'ÙÃÕ' '1-ÙÊ' 'asdf' 'ewr1' 'qwer' 'sdjk' '×ÕÆÅ' '234ac' '5.005' 'ewri2' 'qwqwe' 'wefjn' 'asdÆÙ×' 'gist.c' 'gist.h' 'qwerti' 'ÆÙ×-ÐÒ' 'Ë5ÅÎ45' '234.435' ':8100/?' 'qwe-wer' 'readlin' 'www.com' '+4.0e-10' 'asdf-ÆÙ×' 'gist.h.c' 'rewt/ewr' 'æñùù×ÆÙ×' 'qwe@efd.r' '/?ad=qwe&dw' '/wqe-324/ewr' 'aew.werc.ewr' '1aew.werc.ewr' '2aew.werc.ewr' '3aew.werc.ewr' '4aew.werc.ewr' '5aew.werc.ewr' '6aew.werc.ewr' '7aew.werc.ewr' '/usr/local/fff' '/awdf/dwqe/4325' 'ÊÃÕËÊÃÕËÀÊÃÕËÀË' ':8100/?ad=qwe&dw' 'teodor@stack.net' '5aew.werc.ewr:8100/?' ':8100/?ad=qwe&dw=%20%32' 'aew.werc.ewr/?ad=qwe&dw' '1aew.werc.ewr/?ad=qwe&dw' '3aew.werc.ewr/?ad=qwe&dw' '6aew.werc.ewr:8100/?ad=qwe&dw' '7aew.werc.ewr:8100/?ad=qwe&dw=%20%32'
+(1 row)
+
+select txtidxsize(txt2txtidx('345 qw'));
+ txtidxsize 
+------------
+          2
+(1 row)
+
+select txtidxsize(txt2txtidx('Ë5ÅÎ45 æñùù×ÆÙ× ÙÃÕ 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 weÊ ÊÃÕËÊÃÕËÀÊÃÕËÀË.ÃÆÕ 234.435 455 5.005 teodor@stack.net qwe-wer ÆÙ×-ÐÒ asdf-ÆÙ× asdÆÙ× ÕÒÁ-238 1-ÙÊ <fr>qwer jf sdjk<we hjwer <werrwe> ewr1> ewri2 <a href="qwe<qwe>">
+/usr/local/fff /awdf/dwqe/4325 rewt/ewr ×ÕÆÅ/234ac 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'));
+ txtidxsize 
+------------
+         70
+(1 row)
+
+insert into test_txtidx (a) values ('345 qwerty');
+create trigger txtidxupdate before update or insert on test_txtidx
+for each row execute procedure tsearch(a, t);
+insert into test_txtidx (t) values ('345 qwerty');
+select count(*) FROM test_txtidx WHERE a @@ '345&qwerty';
+ count 
+-------
+     1
+(1 row)
+
+select count(*) FROM test_txtidx WHERE a ## '345&qwerty';
+ count 
+-------
+     1
+(1 row)
+
+update test_txtidx set t = null where t = '345 qwerty';
+select count(*) FROM test_txtidx WHERE a ## '345&qwerty';
+ count 
+-------
+     0
+(1 row)
+
+select count(*) FROM test_txtidx WHERE a @@ '345&qwerty';
+ count 
+-------
+     1
+(1 row)
+