Re: Re: Is it possible to specify an ESCAPE for a wildcard search using LIKE ANY (...)

On Tue, Jan 17, 2017 at 1:45 PM, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:

> Tom Lane schrieb am 17.01.2017 um 13:41:
> > Thomas Kellerer <spam_eater(at)gmx(dot)net> writes:
> >> So my question is: Is there any way to specify an alternate wildcard
> escape when using LIKE ANY (..)?
> >
> > No, not with ESCAPE. [ manfully resists temptation to run down SQL
> > committee's ability to design composable syntax ... oops ]
> >
> > You could do what PG does under the hood, which is to run the pattern
> > through like_escape():
> >
> > select *
> > from some_table
> > where name like any (array[like_escape('foo_bar%', '/'),
> > like_escape('bar_foo%', '/')]);
> >
> > If that seems too verbose, maybe build a function to apply like_escape
> > to each member of an array.
>
> OK, thanks.
>
> I was trying to avoid to actually change the input list, but apparently
> there is no other way.
>

If you don't want to touch the array, you can do something like this:

select *
from tablename as t
where exists (select from unnest($1) as u(x) where t.name like u.x escape
'/');
--

Vik Fearing +33 6 46 75 15
36http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et
Support