Question with combining ANY with ilike

Rusty Conover <rconover@infogears.com> writes:

I'd like to specify a pattern then apply that pattern to match each
element of an array:

rconover=# select 'foobar%' ~~ ANY (ARRAY['bar', 'cat', 'foobar:asdf']);
?column?
----------
f
(1 row)

I'd like the the pattern would be evaluated against all of the array
elements, but the order of parameters for the ~~ operator when
combined with the ANY command is backwards to what I need.

Create a reverse-LIKE operator and underlying one-line SQL function.

regards, tom lane

On Dec 12, 2008, at 6:12 AM, Tom Lane wrote:

Rusty Conover <rconover@infogears.com> writes:

I'd like to specify a pattern then apply that pattern to match each
element of an array:

rconover=# select 'foobar%' ~~ ANY (ARRAY['bar', 'cat',
'foobar:asdf']);
?column?
----------
f
(1 row)

I'd like the the pattern would be evaluated against all of the array
elements, but the order of parameters for the ~~ operator when
combined with the ANY command is backwards to what I need.

Create a reverse-LIKE operator and underlying one-line SQL function.

regards, tom lane

Thanks Tom.

For the benefit of future searches I used:

create function reverse_ilike(text, text) RETURNS boolean AS 'select
$2 ilike $1;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;

create operator ~~*^ (PROCEDURE = reverse_ilike, LEFTARG = text,
RIGHTARG = text);

Best,

Rusty
--
Rusty Conover
rconover@infogears.com
InfoGears Inc / GearBuyer.com / FootwearBuyer.com
http://www.infogears.com
http://www.gearbuyer.com
http://www.footwearbuyer.com