Обсуждение: Question with combining ANY with ilike
Hi PostgreSQL, 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. Do you how I can make it work so that I could write a pattern and match it against the array elements using the ANY command? Thanks, Rusty -- Rusty Conover rconover@infogears.com InfoGears Inc / GearBuyer.com / FootwearBuyer.com http://www.infogears.com http://www.gearbuyer.com http://www.footwearbuyer.com
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 eachelement 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 arrayelements, but the order of parameters for the ~~ operator whencombined 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
InfoGears Inc / GearBuyer.com / FootwearBuyer.com