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

Commit0969dc8

Browse files
committed
Allow LIKE/ILIKE to appear in more places in a query.
Fabien COELHO
1 parent6165bba commit0969dc8

File tree

3 files changed

+83
-7
lines changed

3 files changed

+83
-7
lines changed

‎src/backend/parser/gram.y

Lines changed: 24 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.449 2004/03/17 20:48:42 tgl Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.450 2004/04/05 03:07:26 momjian Exp $
1515
*
1616
* HISTORY
1717
* AUTHORDATEMAJOR EVENT
@@ -194,7 +194,7 @@ static void doNegateFloat(Value *v);
194194
database_nameaccess_method_clauseaccess_methodattr_name
195195
index_namenamefunction_namefile_name
196196

197-
%type<list>func_namehandler_namequal_Opqual_all_Op
197+
%type<list>func_namehandler_namequal_Opqual_all_Opsubquery_Op
198198
opt_classopt_validator
199199

200200
%type<range>qualified_nameOptConstrFromTable
@@ -5692,7 +5692,7 @@ r_expr: row IN_P select_with_parens
56925692
/* Stick a NOT on top*/
56935693
$$ = (Node *)makeA_Expr(AEXPR_NOT, NIL,NULL, (Node *) n);
56945694
}
5695-
| rowqual_all_Op sub_type select_with_parens
5695+
| rowsubquery_Op sub_type select_with_parens
56965696
%precOp
56975697
{
56985698
SubLink *n =makeNode(SubLink);
@@ -5702,7 +5702,7 @@ r_expr: row IN_P select_with_parens
57025702
n->subselect = $4;
57035703
$$ = (Node *)n;
57045704
}
5705-
| rowqual_all_Op select_with_parens
5705+
| rowsubquery_Op select_with_parens
57065706
%precOp
57075707
{
57085708
SubLink *n =makeNode(SubLink);
@@ -5712,7 +5712,7 @@ r_expr: row IN_P select_with_parens
57125712
n->subselect = $3;
57135713
$$ = (Node *)n;
57145714
}
5715-
| rowqual_all_Op row
5715+
| rowsubquery_Op row
57165716
%precOp
57175717
{
57185718
$$ =makeRowExpr($2, $1, $3);
@@ -5807,6 +5807,23 @@ qual_all_Op:
58075807
| OPERATOR'(' any_operator')'{ $$ = $3; }
58085808
;
58095809

5810+
subquery_Op:
5811+
all_Op { $$ =makeList1(makeString($1)); }
5812+
| OPERATOR'(' any_operator')'{ $$ = $3; }
5813+
| LIKE { $$ =makeList1(makeString("~~")); }
5814+
| NOT LIKE { $$ =makeList1(makeString("!~~")); }
5815+
| ILIKE { $$ =makeList1(makeString("~~*")); }
5816+
| NOT ILIKE { $$ =makeList1(makeString("!~~*")); }
5817+
/* cannot put SIMILAR TO here, because SIMILAR TO is a hack.
5818+
* the regular expression is preprocessed by a function (similar_escape),
5819+
* and the ~ operator for posix regular expressions is used.
5820+
* x SIMILAR TO y -> x ~ similar_escape(y)
5821+
* this transformation is made on the fly by the parser upwards.
5822+
* however the SubLink structure which handles any/some/all stuff
5823+
* is not ready for such a thing.
5824+
*/
5825+
;
5826+
58105827
/*
58115828
* General expressions
58125829
* This is the heart of the expression syntax.
@@ -6132,7 +6149,7 @@ a_expr:c_expr{ $$ = $1; }
61326149
$$ = n;
61336150
}
61346151
}
6135-
| a_exprqual_all_Op sub_type select_with_parens %prec Op
6152+
| a_exprsubquery_Op sub_type select_with_parens %prec Op
61366153
{
61376154
SubLink *n =makeNode(SubLink);
61386155
n->subLinkType = $3;
@@ -6141,7 +6158,7 @@ a_expr:c_expr{ $$ = $1; }
61416158
n->subselect = $4;
61426159
$$ = (Node *)n;
61436160
}
6144-
| a_exprqual_all_Op sub_type'(' a_expr')' %prec Op
6161+
| a_exprsubquery_Op sub_type'(' a_expr')' %prec Op
61456162
{
61466163
if ($3 == ANY_SUBLINK)
61476164
$$ = (Node *)makeA_Expr(AEXPR_OP_ANY, $2, $1, $5);

‎src/test/regress/expected/arrays.out

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -377,3 +377,52 @@ select * from arr_tbl where f1 > '{1,2,3}' and f1 <= '{1,5,3}';
377377

378378
-- note: if above select doesn't produce the expected tuple order,
379379
-- then you didn't get an indexscan plan, and something is busted.
380+
-- test [not] (like|ilike) (any|all) (...)
381+
select 'foo' like any (array['%a', '%o']); -- t
382+
?column?
383+
----------
384+
t
385+
(1 row)
386+
387+
select 'foo' like any (array['%a', '%b']); -- f
388+
?column?
389+
----------
390+
f
391+
(1 row)
392+
393+
select 'foo' like all (array['f%', '%o']); -- t
394+
?column?
395+
----------
396+
t
397+
(1 row)
398+
399+
select 'foo' like all (array['f%', '%b']); -- f
400+
?column?
401+
----------
402+
f
403+
(1 row)
404+
405+
select 'foo' not like any (array['%a', '%b']); -- t
406+
?column?
407+
----------
408+
t
409+
(1 row)
410+
411+
select 'foo' not like all (array['%a', '%o']); -- f
412+
?column?
413+
----------
414+
f
415+
(1 row)
416+
417+
select 'foo' ilike any (array['%A', '%O']); -- t
418+
?column?
419+
----------
420+
t
421+
(1 row)
422+
423+
select 'foo' ilike all (array['F%', '%O']); -- t
424+
?column?
425+
----------
426+
t
427+
(1 row)
428+

‎src/test/regress/sql/arrays.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -183,3 +183,13 @@ set enable_seqscan to off;
183183
select*from arr_tblwhere f1>'{1,2,3}'and f1<='{1,5,3}';
184184
-- note: if above select doesn't produce the expected tuple order,
185185
-- then you didn't get an indexscan plan, and something is busted.
186+
187+
-- test [not] (like|ilike) (any|all) (...)
188+
select'foo'like any (array['%a','%o']);-- t
189+
select'foo'like any (array['%a','%b']);-- f
190+
select'foo'like all (array['f%','%o']);-- t
191+
select'foo'like all (array['f%','%b']);-- f
192+
select'foo' notlike any (array['%a','%b']);-- t
193+
select'foo' notlike all (array['%a','%o']);-- f
194+
select'foo' ilike any (array['%A','%O']);-- t
195+
select'foo' ilike all (array['F%','%O']);-- t

0 commit comments

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