Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6f5b8be

Browse files
committed
Make contrib/pg_trgm also support regex searches with GiST indexes.
This wasn't addressed in the original patch, but it doesn't take verymuch additional code to cover the case, so let's get it done.Since pg_trgm 1.1 hasn't been released yet, I just changed the definitionof what's in it, rather than inventing a 1.2.
1 parente543631 commit6f5b8be

File tree

10 files changed

+352
-35
lines changed

10 files changed

+352
-35
lines changed

‎contrib/pg_trgm/expected/pg_trgm.out

Lines changed: 132 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3706,3 +3706,135 @@ select * from test2 where t ilike 'qua%';
37063706
quark
37073707
(1 row)
37083708

3709+
select * from test2 where t like '%z foo bar%';
3710+
t
3711+
-------------
3712+
z foo bar
3713+
(1 row)
3714+
3715+
select * from test2 where t like ' z foo%';
3716+
t
3717+
-------------
3718+
z foo bar
3719+
(1 row)
3720+
3721+
explain (costs off)
3722+
select * from test2 where t ~ '[abc]{3}';
3723+
QUERY PLAN
3724+
------------------------------------------
3725+
Index Scan using test2_idx_gist on test2
3726+
Index Cond: (t ~ '[abc]{3}'::text)
3727+
(2 rows)
3728+
3729+
explain (costs off)
3730+
select * from test2 where t ~* 'DEF';
3731+
QUERY PLAN
3732+
------------------------------------------
3733+
Index Scan using test2_idx_gist on test2
3734+
Index Cond: (t ~* 'DEF'::text)
3735+
(2 rows)
3736+
3737+
select * from test2 where t ~ '[abc]{3}';
3738+
t
3739+
--------
3740+
abcdef
3741+
(1 row)
3742+
3743+
select * from test2 where t ~ 'a[bc]+d';
3744+
t
3745+
--------
3746+
abcdef
3747+
(1 row)
3748+
3749+
select * from test2 where t ~ '(abc)*$';
3750+
t
3751+
-------------
3752+
abcdef
3753+
quark
3754+
z foo bar
3755+
(3 rows)
3756+
3757+
select * from test2 where t ~* 'DEF';
3758+
t
3759+
--------
3760+
abcdef
3761+
(1 row)
3762+
3763+
select * from test2 where t ~ 'dEf';
3764+
t
3765+
---
3766+
(0 rows)
3767+
3768+
select * from test2 where t ~* '^q';
3769+
t
3770+
-------
3771+
quark
3772+
(1 row)
3773+
3774+
select * from test2 where t ~* '[abc]{3}[def]{3}';
3775+
t
3776+
--------
3777+
abcdef
3778+
(1 row)
3779+
3780+
select * from test2 where t ~* 'ab[a-z]{3}';
3781+
t
3782+
--------
3783+
abcdef
3784+
(1 row)
3785+
3786+
select * from test2 where t ~* '(^| )qua';
3787+
t
3788+
-------
3789+
quark
3790+
(1 row)
3791+
3792+
select * from test2 where t ~ 'q.*rk$';
3793+
t
3794+
-------
3795+
quark
3796+
(1 row)
3797+
3798+
select * from test2 where t ~ 'q';
3799+
t
3800+
-------
3801+
quark
3802+
(1 row)
3803+
3804+
select * from test2 where t ~ '[a-z]{3}';
3805+
t
3806+
-------------
3807+
abcdef
3808+
quark
3809+
z foo bar
3810+
(3 rows)
3811+
3812+
select * from test2 where t ~* '(a{10}|b{10}|c{10}){10}';
3813+
t
3814+
---
3815+
(0 rows)
3816+
3817+
select * from test2 where t ~ 'z foo bar';
3818+
t
3819+
-------------
3820+
z foo bar
3821+
(1 row)
3822+
3823+
select * from test2 where t ~ ' z foo bar';
3824+
t
3825+
-------------
3826+
z foo bar
3827+
(1 row)
3828+
3829+
select * from test2 where t ~ ' z foo bar';
3830+
t
3831+
-------------
3832+
z foo bar
3833+
(1 row)
3834+
3835+
select * from test2 where t ~ ' z foo';
3836+
t
3837+
-------------
3838+
z foo bar
3839+
(1 row)
3840+

‎contrib/pg_trgm/pg_trgm--1.0--1.1.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3,6 +3,10 @@
33
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
44
\echo Use"ALTER EXTENSION pg_trgm UPDATE TO '1.1'" to load this file. \quit
55

6+
ALTEROPERATOR FAMILY gist_trgm_ops USING gist ADD
7+
OPERATOR5 pg_catalog.~ (text,text),
8+
OPERATOR6 pg_catalog.~* (text,text);
9+
610
ALTEROPERATOR FAMILY gin_trgm_ops USING gin ADD
711
OPERATOR5 pg_catalog.~ (text,text),
812
OPERATOR6 pg_catalog.~* (text,text);

‎contrib/pg_trgm/pg_trgm--1.1.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -132,6 +132,12 @@ ALTER OPERATOR FAMILY gist_trgm_ops USING gist ADD
132132
OPERATOR4 pg_catalog.~~* (text,text),
133133
FUNCTION8 (text,text) gtrgm_distance (internal,text,int,oid);
134134

135+
-- Add operators that are new in 9.3.
136+
137+
ALTEROPERATOR FAMILY gist_trgm_ops USING gist ADD
138+
OPERATOR5 pg_catalog.~ (text,text),
139+
OPERATOR6 pg_catalog.~* (text,text);
140+
135141
-- support functions for gin
136142
CREATEFUNCTIONgin_extract_value_trgm(text, internal)
137143
RETURNS internal

‎contrib/pg_trgm/sql/pg_trgm.sql

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -90,3 +90,26 @@ select * from test2 where t like '%bcd%';
9090
select*from test2where tlike E'%\\bcd%';
9191
select*from test2where t ilike'%BCD%';
9292
select*from test2where t ilike'qua%';
93+
select*from test2where tlike'%z foo bar%';
94+
select*from test2where tlike' z foo%';
95+
explain (costs off)
96+
select*from test2where t ~'[abc]{3}';
97+
explain (costs off)
98+
select*from test2where t ~*'DEF';
99+
select*from test2where t ~'[abc]{3}';
100+
select*from test2where t ~'a[bc]+d';
101+
select*from test2where t ~'(abc)*$';
102+
select*from test2where t ~*'DEF';
103+
select*from test2where t ~'dEf';
104+
select*from test2where t ~*'^q';
105+
select*from test2where t ~*'[abc]{3}[def]{3}';
106+
select*from test2where t ~*'ab[a-z]{3}';
107+
select*from test2where t ~*'(^| )qua';
108+
select*from test2where t ~'q.*rk$';
109+
select*from test2where t ~'q';
110+
select*from test2where t ~'[a-z]{3}';
111+
select*from test2where t ~*'(a{10}|b{10}|c{10}){10}';
112+
select*from test2where t ~'z foo bar';
113+
select*from test2where t ~' z foo bar';
114+
select*from test2where t ~' z foo bar';
115+
select*from test2where t ~' z foo';

‎contrib/pg_trgm/trgm.h

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -113,8 +113,9 @@ extern TRGM *generate_trgm(char *str, int slen);
113113
externTRGM*generate_wildcard_trgm(constchar*str,intslen);
114114
externfloat4cnt_sml(TRGM*trg1,TRGM*trg2);
115115
externbooltrgm_contained_by(TRGM*trg1,TRGM*trg2);
116-
externTRGM*createTrgmNFA(text*text_re,TrgmPackedGraph**graph,
117-
Oidcollation);
116+
externbool*trgm_presence_map(TRGM*query,TRGM*key);
117+
externTRGM*createTrgmNFA(text*text_re,Oidcollation,
118+
TrgmPackedGraph**graph,MemoryContextrcontext);
118119
externbooltrigramsMatchGraph(TrgmPackedGraph*graph,bool*check);
119120

120121
#endif/* __TRGM_H__ */

‎contrib/pg_trgm/trgm_gin.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -115,7 +115,8 @@ gin_extract_query_trgm(PG_FUNCTION_ARGS)
115115
#endif
116116
/* FALL THRU */
117117
caseRegExpStrategyNumber:
118-
trg=createTrgmNFA(val,&graph,PG_GET_COLLATION());
118+
trg=createTrgmNFA(val,PG_GET_COLLATION(),
119+
&graph,CurrentMemoryContext);
119120
if (trg&&ARRNELEM(trg)>0)
120121
{
121122
/*

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp