Dear Patchers,
Please find attached a patch which allows "LIKE/ILIKE/NOT LIKE/NOT ILIKE"
as operators for ANY/SOME/ALL constructs.
SELECT 'foo' LIKE ANY (ARRAY['%a','%o']);
It should also fix some other places where a "~~" operator was allowed,
but not the LIKE operator in the very same place.
However, this patch does not address "SIMILAR TO", as its "hacked"
implementation does not fit the already existing structures for ANY/ALL.
I've added a comment about this in the regression tests.
It validates for me against current CVS head.
Have a nice day,
--
Fabien Coelho.
*** ./src/backend/parser/gram.y.orig Thu Mar 18 09:01:11 2004
--- ./src/backend/parser/gram.y Thu Mar 25 16:18:05 2004
***************
*** 5805,5810 ****
--- 5805,5814 ----
all_Op
{ $$ = makeList1(makeString($1)); }
| OPERATOR '(' any_operator ')' { $$ = $3; }
+ | LIKE { $$ = makeList1(makeString("~~")); }
+ | NOT LIKE { $$ = makeList1(makeString("!~~")); }
+ | ILIKE { $$ = makeList1(makeString("~~*")); }
+ | NOT ILIKE { $$ = makeList1(makeString("!~~*")); }
;
/*
*** ./src/test/regress/expected/arrays.out.orig Thu Mar 25 15:59:21 2004
--- ./src/test/regress/expected/arrays.out Thu Mar 25 16:29:03 2004
***************
*** 377,379 ****
--- 377,435 ----
-- note: if above select doesn't produce the expected tuple order,
-- then you didn't get an indexscan plan, and something is busted.
+ -- test [not] (like|ilike) (any|all) (...)
+ select 'foo' like any (array['%a', '%o']); -- t
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select 'foo' like any (array['%a', '%b']); -- f
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ select 'foo' like all (array['f%', '%o']); -- t
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select 'foo' like all (array['f%', '%b']); -- f
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ select 'foo' not like any (array['%a', '%b']); -- t
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select 'foo' not like all (array['%a', '%o']); -- f
+ ?column?
+ ----------
+ f
+ (1 row)
+
+ select 'foo' ilike any (array['%A', '%O']); -- t
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ select 'foo' ilike all (array['F%', '%O']); -- t
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ -- it does not work for "similar to" because "similar to" is a hack.
+ -- the regular expression is preprocessed by a function (similar_escape),
+ -- and the ~ operator for posix regular expressions is used.
+ -- x SIMILAR TO y -> x ~ similar_escape(y)
+ -- this transformation is made on the fly by the parser.
+ -- however the SubLink structure which handles any/all stuff
+ -- is not ready for such a thing.
*** ./src/test/regress/sql/arrays.sql.orig Mon Aug 18 01:43:27 2003
--- ./src/test/regress/sql/arrays.sql Thu Mar 25 16:25:50 2004
***************
*** 183,185 ****
--- 183,203 ----
select * from arr_tbl where f1 > '{1,2,3}' and f1 <= '{1,5,3}';
-- note: if above select doesn't produce the expected tuple order,
-- then you didn't get an indexscan plan, and something is busted.
+
+ -- test [not] (like|ilike) (any|all) (...)
+ select 'foo' like any (array['%a', '%o']); -- t
+ select 'foo' like any (array['%a', '%b']); -- f
+ select 'foo' like all (array['f%', '%o']); -- t
+ select 'foo' like all (array['f%', '%b']); -- f
+ select 'foo' not like any (array['%a', '%b']); -- t
+ select 'foo' not like all (array['%a', '%o']); -- f
+ select 'foo' ilike any (array['%A', '%O']); -- t
+ select 'foo' ilike all (array['F%', '%O']); -- t
+
+ -- it does not work for "similar to" because "similar to" is a hack.
+ -- the regular expression is preprocessed by a function (similar_escape),
+ -- and the ~ operator for posix regular expressions is used.
+ -- x SIMILAR TO y -> x ~ similar_escape(y)
+ -- this transformation is made on the fly by the parser.
+ -- however the SubLink structure which handles any/all stuff
+ -- is not ready for such a thing.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
