OSDN Git Service

(root) / ludiafuncs / ludia_funcs.git / commitdiff
? search:
summary | shortlog | log | commit | commitdiff | tree
raw | patch | inline | side by side (parent: d9baa3a)
Add regression tests for pg_bigm search with AND, OR or NOT condition.
authorMasaoFujii <masao.fujii@gmail.com>
Fri, 4 Sep 2015 13:57:00 +0000 (22:57 +0900)
committerMasaoFujii <masao.fujii@gmail.com>
Fri, 4 Sep 2015 13:57:00 +0000 (22:57 +0900)
Ayumi Ishii

expected/pg_bigm.out patch | blob | history
sql/pg_bigm.sql patch | blob | history

diff --git a/expected/pg_bigm.out b/expected/pg_bigm.out
index fdfe82b..c61b832 100644 (file)
--- a/expected/pg_bigm.out
+++ b/expected/pg_bigm.out
@@ -16,21 +16,21 @@ CREATE INDEX char_tbl_idx ON char_tbl USING gin (pgs2norm(col1) gin_bigm_ops,
                                                                    pgs2norm(col2) gin_bigm_ops,
                                                                    pgs2norm(col3) gin_bigm_ops);
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'v(株)', col1) FROM char_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('v(株)'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('v(株)'));
      pgs2snippet1      
 -----------------------
  ロﾊAbＣｄ1２③★Ⅳ㈱★');
 (1 row)
 
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, '②⓪', col1) FROM char_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('②⓪'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('②⓪'));
  pgs2snippet1  
 ---------------
  ★ 20★13.04.05
 (1 row)
 
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'ｱﾌﾟﾘｹｰｼｮﾝ', col1) FROM char_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱﾌﾟﾘｹｰｼｮﾝ'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱﾌﾟﾘｹｰｼｮﾝ'));
       pgs2snippet1      
 ------------------------
  ト★アプリケーション★側
@@ -44,21 +44,21 @@ CREATE INDEX varchar_tbl_idx ON varchar_tbl USING gin (pgs2norm(col1) gin_bigm_o
                                                                            pgs2norm(col2) gin_bigm_ops,
                                                                            pgs2norm(col3) gin_bigm_ops);
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'v(株)', col1) FROM varchar_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('v(株)'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('v(株)'));
      pgs2snippet1      
 -----------------------
  ロﾊAbＣｄ1２③★Ⅳ㈱★');
 (1 row)
 
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, '②⓪', col1) FROM varchar_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('②⓪'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('②⓪'));
  pgs2snippet1  
 ---------------
  ★ 20★13.04.05
 (1 row)
 
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'ｱﾌﾟﾘｹｰｼｮﾝ', col1) FROM varchar_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱﾌﾟﾘｹｰｼｮﾝ'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱﾌﾟﾘｹｰｼｮﾝ'));
       pgs2snippet1      
 ------------------------
  ト★アプリケーション★側
@@ -72,51 +72,198 @@ CREATE INDEX text_tbl_idx ON text_tbl USING gin (pgs2norm(col1) gin_bigm_ops,
                                                                   pgs2norm(col2) gin_bigm_ops,
                                                                   pgs2norm(col3) gin_bigm_ops);
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'v(株)', col1) FROM text_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('v(株)'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('v(株)'));
      pgs2snippet1      
 -----------------------
  ロﾊAbＣｄ1２③★Ⅳ㈱★');
 (1 row)
 
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, '②⓪', col1) FROM text_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('②⓪'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('②⓪'));
  pgs2snippet1  
 ---------------
  ★ 20★13.04.05
 (1 row)
 
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'ｱﾌﾟﾘｹｰｼｮﾝ', col1) FROM text_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱﾌﾟﾘｹｰｼｮﾝ'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱﾌﾟﾘｹｰｼｮﾝ'));
       pgs2snippet1      
 ------------------------
  ト★アプリケーション★側
 (1 row)
 
+-- Test the case where condition has "AND".
+SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col1) LIKE likequery(pgs2norm('解釈')) AND
+    pgs2norm(col1) LIKE likequery(pgs2norm('実装')) AND
+    pgs2norm(col1) LIKE likequery(pgs2norm('場合'));
+ count 
+-------
+     1
+(1 row)
+
+EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col1) LIKE likequery(pgs2norm('解釈')) AND
+    pgs2norm(col1) LIKE likequery(pgs2norm('実装')) AND
+    pgs2norm(col1) LIKE likequery(pgs2norm('場合'));
+                                                                  QUERY PLAN                                                                  
+----------------------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+   ->  Bitmap Heap Scan on text_tbl
+         Recheck Cond: ((pgs2norm(col1) ~~ '%解釈%'::text) AND (pgs2norm(col1) ~~ '%実装%'::text) AND (pgs2norm(col1) ~~ '%場合%'::text))
+         ->  Bitmap Index Scan on text_tbl_idx
+               Index Cond: ((pgs2norm(col1) ~~ '%解釈%'::text) AND (pgs2norm(col1) ~~ '%実装%'::text) AND (pgs2norm(col1) ~~ '%場合%'::text))
+(5 rows)
+
+SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col1) LIKE likequery(pgs2norm('解釈')) AND
+    pgs2norm(col2) LIKE likequery(pgs2norm('ﾕｰｻ゛')) AND
+    pgs2norm(col3) LIKE likequery(pgs2norm('ＳｑＬ'));
+ count 
+-------
+     1
+(1 row)
+
+EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col1) LIKE likequery(pgs2norm('解釈')) AND
+    pgs2norm(col2) LIKE likequery(pgs2norm('ﾕｰｻ゛')) AND
+    pgs2norm(col3) LIKE likequery(pgs2norm('ＳｑＬ'));
+                                                                  QUERY PLAN                                                                   
+-----------------------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+   ->  Bitmap Heap Scan on text_tbl
+         Recheck Cond: ((pgs2norm(col1) ~~ '%解釈%'::text) AND (pgs2norm(col2) ~~ '%ユーザ%'::text) AND (pgs2norm(col3) ~~ '%sql%'::text))
+         ->  Bitmap Index Scan on text_tbl_idx
+               Index Cond: ((pgs2norm(col1) ~~ '%解釈%'::text) AND (pgs2norm(col2) ~~ '%ユーザ%'::text) AND (pgs2norm(col3) ~~ '%sql%'::text))
+(5 rows)
+
+-- Test the case where condition has "OR".
+SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col2) LIKE likequery(pgs2norm('ﾊ゜ラメータ')) OR
+    pgs2norm(col2) LIKE likequery(pgs2norm('㉜')) OR
+    pgs2norm(col2) LIKE likequery(pgs2norm('  '));
+ count 
+-------
+     8
+(1 row)
+
+EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col2) LIKE likequery(pgs2norm('ﾊ゜ラメータ')) OR
+    pgs2norm(col2) LIKE likequery(pgs2norm('㉜')) OR
+    pgs2norm(col2) LIKE likequery(pgs2norm('  '));
+                                                                QUERY PLAN                                                                
+------------------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+   ->  Bitmap Heap Scan on text_tbl
+         Recheck Cond: ((pgs2norm(col2) ~~ '%パラメータ%'::text) OR (pgs2norm(col2) ~~ '%32%'::text) OR (pgs2norm(col2) ~~ '%  %'::text))
+         ->  BitmapOr
+               ->  Bitmap Index Scan on text_tbl_idx
+                     Index Cond: (pgs2norm(col2) ~~ '%パラメータ%'::text)
+               ->  Bitmap Index Scan on text_tbl_idx
+                     Index Cond: (pgs2norm(col2) ~~ '%32%'::text)
+               ->  Bitmap Index Scan on text_tbl_idx
+                     Index Cond: (pgs2norm(col2) ~~ '%  %'::text)
+(10 rows)
+
+SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col1) LIKE likequery(pgs2norm('ﾊ゜ラメータ')) OR
+    pgs2norm(col2) LIKE likequery(pgs2norm('㉜')) OR
+    pgs2norm(col3) LIKE likequery(pgs2norm('  '));
+ count 
+-------
+     9
+(1 row)
+
+EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col1) LIKE likequery(pgs2norm('ﾊ゜ラメータ')) OR
+    pgs2norm(col2) LIKE likequery(pgs2norm('㉜')) OR
+    pgs2norm(col3) LIKE likequery(pgs2norm('  '));
+                                                                QUERY PLAN                                                                
+------------------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+   ->  Bitmap Heap Scan on text_tbl
+         Recheck Cond: ((pgs2norm(col1) ~~ '%パラメータ%'::text) OR (pgs2norm(col2) ~~ '%32%'::text) OR (pgs2norm(col3) ~~ '%  %'::text))
+         ->  BitmapOr
+               ->  Bitmap Index Scan on text_tbl_idx
+                     Index Cond: (pgs2norm(col1) ~~ '%パラメータ%'::text)
+               ->  Bitmap Index Scan on text_tbl_idx
+                     Index Cond: (pgs2norm(col2) ~~ '%32%'::text)
+               ->  Bitmap Index Scan on text_tbl_idx
+                     Index Cond: (pgs2norm(col3) ~~ '%  %'::text)
+(10 rows)
+
+-- Test the case where condition has "NOT".
+SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col3) LIKE likequery(pgs2norm('ludia')) AND
+    NOT pgs2norm(col3) LIKE likequery(pgs2norm('㊐')) AND
+    NOT pgs2norm(col3) LIKE likequery(pgs2norm('Ⓟ'));
+ count 
+-------
+    11
+(1 row)
+
+EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col3) LIKE likequery(pgs2norm('ludia')) AND
+    NOT pgs2norm(col3) LIKE likequery(pgs2norm('㊐')) AND
+    NOT pgs2norm(col3) LIKE likequery(pgs2norm('Ⓟ'));
+                                        QUERY PLAN                                        
+------------------------------------------------------------------------------------------
+ Aggregate
+   ->  Bitmap Heap Scan on text_tbl
+         Recheck Cond: (pgs2norm(col3) ~~ '%ludia%'::text)
+         Filter: ((pgs2norm(col3) !~~ '%日%'::text) AND (pgs2norm(col3) !~~ '%p%'::text))
+         ->  Bitmap Index Scan on text_tbl_idx
+               Index Cond: (pgs2norm(col3) ~~ '%ludia%'::text)
+(6 rows)
+
+SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col2) LIKE likequery(pgs2norm('ludia')) AND
+    NOT pgs2norm(col3) LIKE likequery(pgs2norm('㊐')) AND
+    NOT pgs2norm(col1) LIKE likequery(pgs2norm('Ⓟ'));
+ count 
+-------
+     8
+(1 row)
+
+EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col2) LIKE likequery(pgs2norm('ludia')) AND
+    NOT pgs2norm(col3) LIKE likequery(pgs2norm('㊐')) AND
+    NOT pgs2norm(col1) LIKE likequery(pgs2norm('Ⓟ'));
+                                        QUERY PLAN                                        
+------------------------------------------------------------------------------------------
+ Aggregate
+   ->  Bitmap Heap Scan on text_tbl
+         Recheck Cond: (pgs2norm(col2) ~~ '%ludia%'::text)
+         Filter: ((pgs2norm(col3) !~~ '%日%'::text) AND (pgs2norm(col1) !~~ '%p%'::text))
+         ->  Bitmap Index Scan on text_tbl_idx
+               Index Cond: (pgs2norm(col2) ~~ '%ludia%'::text)
+(6 rows)
+
 -- Test the case where a multi-column index is created on many columns
 CREATE TABLE mc31_tbl (col1 text, col2 char(256), col3 varchar(256), col4 text,
-                        col5 char(256), col6 varchar(256), col7 text, col8 char(256),
-                        col9 varchar(256), col10 text, col11 char(256), col12 varchar(256),
-                        col13 text, col14 char(256), col15 varchar(256), col16 text,
-                        col17 char(256), col18 varchar(256), col19 text, col20 char(256),
-                        col21 varchar(256), col22 text, col23 char(256), col24 varchar(256),
-                        col25 text, col26 char(256), col27 varchar(256), col28 text,
-                        col29 char(256), col30 varchar(256), col31 text);
+    col5 char(256), col6 varchar(256), col7 text, col8 char(256),
+    col9 varchar(256), col10 text, col11 char(256), col12 varchar(256),
+    col13 text, col14 char(256), col15 varchar(256), col16 text,
+    col17 char(256), col18 varchar(256), col19 text, col20 char(256),
+    col21 varchar(256), col22 text, col23 char(256), col24 varchar(256),
+    col25 text, col26 char(256), col27 varchar(256), col28 text,
+    col29 char(256), col30 varchar(256), col31 text);
 CREATE INDEX mc31_tbl_idx ON mc31_tbl USING gin (pgs2norm(col1) gin_bigm_ops,
-                        pgs2norm(col2) gin_bigm_ops, pgs2norm(col3) gin_bigm_ops,
-                        pgs2norm(col4) gin_bigm_ops, pgs2norm(col5) gin_bigm_ops,
-                        pgs2norm(col6) gin_bigm_ops, pgs2norm(col7) gin_bigm_ops,
-                        pgs2norm(col8) gin_bigm_ops, pgs2norm(col9) gin_bigm_ops,
-                        pgs2norm(col10) gin_bigm_ops, pgs2norm(col11) gin_bigm_ops,
-                        pgs2norm(col12) gin_bigm_ops, pgs2norm(col13) gin_bigm_ops,
-                        pgs2norm(col14) gin_bigm_ops, pgs2norm(col15) gin_bigm_ops,
-                        pgs2norm(col16) gin_bigm_ops, pgs2norm(col17) gin_bigm_ops,
-                        pgs2norm(col18) gin_bigm_ops, pgs2norm(col19) gin_bigm_ops,
-                        pgs2norm(col20) gin_bigm_ops, pgs2norm(col21) gin_bigm_ops,
-                        pgs2norm(col22) gin_bigm_ops, pgs2norm(col23) gin_bigm_ops,
-                        pgs2norm(col24) gin_bigm_ops, pgs2norm(col25) gin_bigm_ops,
-                        pgs2norm(col26) gin_bigm_ops, pgs2norm(col27) gin_bigm_ops,
-                        pgs2norm(col28) gin_bigm_ops, pgs2norm(col29) gin_bigm_ops,
-                        pgs2norm(col30) gin_bigm_ops, pgs2norm(col31) gin_bigm_ops);
+    pgs2norm(col2) gin_bigm_ops, pgs2norm(col3) gin_bigm_ops,
+    pgs2norm(col4) gin_bigm_ops, pgs2norm(col5) gin_bigm_ops,
+    pgs2norm(col6) gin_bigm_ops, pgs2norm(col7) gin_bigm_ops,
+    pgs2norm(col8) gin_bigm_ops, pgs2norm(col9) gin_bigm_ops,
+    pgs2norm(col10) gin_bigm_ops, pgs2norm(col11) gin_bigm_ops,
+    pgs2norm(col12) gin_bigm_ops, pgs2norm(col13) gin_bigm_ops,
+    pgs2norm(col14) gin_bigm_ops, pgs2norm(col15) gin_bigm_ops,
+    pgs2norm(col16) gin_bigm_ops, pgs2norm(col17) gin_bigm_ops,
+    pgs2norm(col18) gin_bigm_ops, pgs2norm(col19) gin_bigm_ops,
+    pgs2norm(col20) gin_bigm_ops, pgs2norm(col21) gin_bigm_ops,
+    pgs2norm(col22) gin_bigm_ops, pgs2norm(col23) gin_bigm_ops,
+    pgs2norm(col24) gin_bigm_ops, pgs2norm(col25) gin_bigm_ops,
+    pgs2norm(col26) gin_bigm_ops, pgs2norm(col27) gin_bigm_ops,
+    pgs2norm(col28) gin_bigm_ops, pgs2norm(col29) gin_bigm_ops,
+    pgs2norm(col30) gin_bigm_ops, pgs2norm(col31) gin_bigm_ops);
 \copy mc31_tbl from 'data/test_tbl_31.txt'
 SELECT col1 FROM mc31_tbl WHERE pgs2norm(col1) like likequery(pgs2norm('＿Ｓ'));
                      col1                     
diff --git a/sql/pg_bigm.sql b/sql/pg_bigm.sql
index d55a5fa..eb51e32 100644 (file)
--- a/sql/pg_bigm.sql
+++ b/sql/pg_bigm.sql
@@ -20,11 +20,11 @@ CREATE INDEX char_tbl_idx ON char_tbl USING gin (pgs2norm(col1) gin_bigm_ops,
                                                                    pgs2norm(col3) gin_bigm_ops);
 
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'v(株)', col1) FROM char_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('v(株)'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('v(株)'));
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, '②⓪', col1) FROM char_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('②⓪'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('②⓪'));
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'ｱﾌﾟﾘｹｰｼｮﾝ', col1) FROM char_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱﾌﾟﾘｹｰｼｮﾝ'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱﾌﾟﾘｹｰｼｮﾝ'));
 
 -- Test the case where the columns with VARCHAR data type were indexed
 -- with pg_bigm and pgs2norm function.
@@ -35,11 +35,11 @@ CREATE INDEX varchar_tbl_idx ON varchar_tbl USING gin (pgs2norm(col1) gin_bigm_o
                                                                            pgs2norm(col3) gin_bigm_ops);
 
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'v(株)', col1) FROM varchar_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('v(株)'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('v(株)'));
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, '②⓪', col1) FROM varchar_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('②⓪'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('②⓪'));
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'ｱﾌﾟﾘｹｰｼｮﾝ', col1) FROM varchar_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱﾌﾟﾘｹｰｼｮﾝ'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱﾌﾟﾘｹｰｼｮﾝ'));
 
 -- Test the case where the columns with TEXT data type were indexed
 -- with pg_bigm and pgs2norm function.
@@ -50,37 +50,91 @@ CREATE INDEX text_tbl_idx ON text_tbl USING gin (pgs2norm(col1) gin_bigm_ops,
                                                                   pgs2norm(col3) gin_bigm_ops);
 
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'v(株)', col1) FROM text_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('v(株)'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('v(株)'));
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, '②⓪', col1) FROM text_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('②⓪'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('②⓪'));
 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'ｱﾌﾟﾘｹｰｼｮﾝ', col1) FROM text_tbl
-                        WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱﾌﾟﾘｹｰｼｮﾝ'));
+    WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱﾌﾟﾘｹｰｼｮﾝ'));
+
+-- Test the case where condition has "AND".
+SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col1) LIKE likequery(pgs2norm('解釈')) AND
+    pgs2norm(col1) LIKE likequery(pgs2norm('実装')) AND
+    pgs2norm(col1) LIKE likequery(pgs2norm('場合'));
+EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col1) LIKE likequery(pgs2norm('解釈')) AND
+    pgs2norm(col1) LIKE likequery(pgs2norm('実装')) AND
+    pgs2norm(col1) LIKE likequery(pgs2norm('場合'));
+SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col1) LIKE likequery(pgs2norm('解釈')) AND
+    pgs2norm(col2) LIKE likequery(pgs2norm('ﾕｰｻ゛')) AND
+    pgs2norm(col3) LIKE likequery(pgs2norm('ＳｑＬ'));
+EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col1) LIKE likequery(pgs2norm('解釈')) AND
+    pgs2norm(col2) LIKE likequery(pgs2norm('ﾕｰｻ゛')) AND
+    pgs2norm(col3) LIKE likequery(pgs2norm('ＳｑＬ'));
+
+-- Test the case where condition has "OR".
+SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col2) LIKE likequery(pgs2norm('ﾊ゜ラメータ')) OR
+    pgs2norm(col2) LIKE likequery(pgs2norm('㉜')) OR
+    pgs2norm(col2) LIKE likequery(pgs2norm('  '));
+EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col2) LIKE likequery(pgs2norm('ﾊ゜ラメータ')) OR
+    pgs2norm(col2) LIKE likequery(pgs2norm('㉜')) OR
+    pgs2norm(col2) LIKE likequery(pgs2norm('  '));
+SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col1) LIKE likequery(pgs2norm('ﾊ゜ラメータ')) OR
+    pgs2norm(col2) LIKE likequery(pgs2norm('㉜')) OR
+    pgs2norm(col3) LIKE likequery(pgs2norm('  '));
+EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col1) LIKE likequery(pgs2norm('ﾊ゜ラメータ')) OR
+    pgs2norm(col2) LIKE likequery(pgs2norm('㉜')) OR
+    pgs2norm(col3) LIKE likequery(pgs2norm('  '));
+
+-- Test the case where condition has "NOT".
+SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col3) LIKE likequery(pgs2norm('ludia')) AND
+    NOT pgs2norm(col3) LIKE likequery(pgs2norm('㊐')) AND
+    NOT pgs2norm(col3) LIKE likequery(pgs2norm('Ⓟ'));
+EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col3) LIKE likequery(pgs2norm('ludia')) AND
+    NOT pgs2norm(col3) LIKE likequery(pgs2norm('㊐')) AND
+    NOT pgs2norm(col3) LIKE likequery(pgs2norm('Ⓟ'));
+SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col2) LIKE likequery(pgs2norm('ludia')) AND
+    NOT pgs2norm(col3) LIKE likequery(pgs2norm('㊐')) AND
+    NOT pgs2norm(col1) LIKE likequery(pgs2norm('Ⓟ'));
+EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
+    pgs2norm(col2) LIKE likequery(pgs2norm('ludia')) AND
+    NOT pgs2norm(col3) LIKE likequery(pgs2norm('㊐')) AND
+    NOT pgs2norm(col1) LIKE likequery(pgs2norm('Ⓟ'));
 
 -- Test the case where a multi-column index is created on many columns
 CREATE TABLE mc31_tbl (col1 text, col2 char(256), col3 varchar(256), col4 text,
-                        col5 char(256), col6 varchar(256), col7 text, col8 char(256),
-                        col9 varchar(256), col10 text, col11 char(256), col12 varchar(256),
-                        col13 text, col14 char(256), col15 varchar(256), col16 text,
-                        col17 char(256), col18 varchar(256), col19 text, col20 char(256),
-                        col21 varchar(256), col22 text, col23 char(256), col24 varchar(256),
-                        col25 text, col26 char(256), col27 varchar(256), col28 text,
-                        col29 char(256), col30 varchar(256), col31 text);
+    col5 char(256), col6 varchar(256), col7 text, col8 char(256),
+    col9 varchar(256), col10 text, col11 char(256), col12 varchar(256),
+    col13 text, col14 char(256), col15 varchar(256), col16 text,
+    col17 char(256), col18 varchar(256), col19 text, col20 char(256),
+    col21 varchar(256), col22 text, col23 char(256), col24 varchar(256),
+    col25 text, col26 char(256), col27 varchar(256), col28 text,
+    col29 char(256), col30 varchar(256), col31 text);
 CREATE INDEX mc31_tbl_idx ON mc31_tbl USING gin (pgs2norm(col1) gin_bigm_ops,
-                        pgs2norm(col2) gin_bigm_ops, pgs2norm(col3) gin_bigm_ops,
-                        pgs2norm(col4) gin_bigm_ops, pgs2norm(col5) gin_bigm_ops,
-                        pgs2norm(col6) gin_bigm_ops, pgs2norm(col7) gin_bigm_ops,
-                        pgs2norm(col8) gin_bigm_ops, pgs2norm(col9) gin_bigm_ops,
-                        pgs2norm(col10) gin_bigm_ops, pgs2norm(col11) gin_bigm_ops,
-                        pgs2norm(col12) gin_bigm_ops, pgs2norm(col13) gin_bigm_ops,
-                        pgs2norm(col14) gin_bigm_ops, pgs2norm(col15) gin_bigm_ops,
-                        pgs2norm(col16) gin_bigm_ops, pgs2norm(col17) gin_bigm_ops,
-                        pgs2norm(col18) gin_bigm_ops, pgs2norm(col19) gin_bigm_ops,
-                        pgs2norm(col20) gin_bigm_ops, pgs2norm(col21) gin_bigm_ops,
-                        pgs2norm(col22) gin_bigm_ops, pgs2norm(col23) gin_bigm_ops,
-                        pgs2norm(col24) gin_bigm_ops, pgs2norm(col25) gin_bigm_ops,
-                        pgs2norm(col26) gin_bigm_ops, pgs2norm(col27) gin_bigm_ops,
-                        pgs2norm(col28) gin_bigm_ops, pgs2norm(col29) gin_bigm_ops,
-                        pgs2norm(col30) gin_bigm_ops, pgs2norm(col31) gin_bigm_ops);
+    pgs2norm(col2) gin_bigm_ops, pgs2norm(col3) gin_bigm_ops,
+    pgs2norm(col4) gin_bigm_ops, pgs2norm(col5) gin_bigm_ops,
+    pgs2norm(col6) gin_bigm_ops, pgs2norm(col7) gin_bigm_ops,
+    pgs2norm(col8) gin_bigm_ops, pgs2norm(col9) gin_bigm_ops,
+    pgs2norm(col10) gin_bigm_ops, pgs2norm(col11) gin_bigm_ops,
+    pgs2norm(col12) gin_bigm_ops, pgs2norm(col13) gin_bigm_ops,
+    pgs2norm(col14) gin_bigm_ops, pgs2norm(col15) gin_bigm_ops,
+    pgs2norm(col16) gin_bigm_ops, pgs2norm(col17) gin_bigm_ops,
+    pgs2norm(col18) gin_bigm_ops, pgs2norm(col19) gin_bigm_ops,
+    pgs2norm(col20) gin_bigm_ops, pgs2norm(col21) gin_bigm_ops,
+    pgs2norm(col22) gin_bigm_ops, pgs2norm(col23) gin_bigm_ops,
+    pgs2norm(col24) gin_bigm_ops, pgs2norm(col25) gin_bigm_ops,
+    pgs2norm(col26) gin_bigm_ops, pgs2norm(col27) gin_bigm_ops,
+    pgs2norm(col28) gin_bigm_ops, pgs2norm(col29) gin_bigm_ops,
+    pgs2norm(col30) gin_bigm_ops, pgs2norm(col31) gin_bigm_ops);
 \copy mc31_tbl from 'data/test_tbl_31.txt'
 
 SELECT col1 FROM mc31_tbl WHERE pgs2norm(col1) like likequery(pgs2norm('＿Ｓ'));
This is the main ludia_funcs git repository.
About OSDN
Find Software
Develop Software
Help