On Tue, Jan 17, 2017 at 1:45 PM, Thomas Kellerer <spam_eater@xxxxxxx> wrote:
-- Tom Lane schrieb am 17.01.2017 um 13:41:
> Thomas Kellerer <spam_eater@xxxxxxx> 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 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
