Re: limitation using LIKE on ANY(array)
От | K C Lau |
---|---|
Тема | Re: limitation using LIKE on ANY(array) |
Дата | |
Msg-id | 6.2.1.2.0.20060325094356.04de84d8@localhost обсуждение исходный текст |
Ответ на | Re: limitation using LIKE on ANY(array) (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-performance |
Thank you very much, Tom. We'll try it and report if there is any significant impact performance-wise. Best regards, KC. At 00:25 06/03/25, Tom Lane wrote: >K C Lau <kclau60@netvigator.com> writes: > > Indeed, I get rejected even with: > > .. WHERE ANY(array) = 'xx' > > > It would only work as documented in the manual (8.10.5): > > SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter); > >That's not changing any time soon; the SQL spec defines only the second >syntax for ANY, and I believe there would be syntactic ambiguity if we >tried to allow the other. > > > With 8.1.3, I get an error when trying to do this on a Text[] column : > > .. WHERE ANY(array) LIKE 'xx%' > >If you're really intent on doing that, make an operator for "reverse >LIKE" and use it with the ANY on the right-hand side. > >regression=# create function rlike(text,text) returns bool as >regression-# 'select $2 like $1' language sql strict immutable; >CREATE FUNCTION >regression=# create operator ~~~ (procedure = rlike, leftarg = text, >regression(# rightarg = text, commutator = ~~); >CREATE OPERATOR >regression=# select 'xx%' ~~~ any(array['aaa','bbb']); > ?column? >---------- > f >(1 row) > >regression=# select 'xx%' ~~~ any(array['aaa','xxb']); > ?column? >---------- > t >(1 row) > >regression=# > > regards, tom lane
В списке pgsql-performance по дате отправления: