Re: Question with combining ANY with ilike

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

> Rusty Conover <rconover(at)infogears(dot)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(at)infogears(dot)com
InfoGears Inc / GearBuyer.com / FootwearBuyer.com
http://www.infogears.com
http://www.gearbuyer.com
http://www.footwearbuyer.com