Re: [GENERAL] What's the best way in postgres to use ANY() with LIKE '%'?

Ryan Murphy <ryanfmurphy@gmail.com> writes:
> But is there any way to do:
> select * from post
> where any(tags) LIKE 'music%';
> ??
> This doesn't work because ANY is only allowed on the right.

Yeah.  The traditional answer is "make yourself a reverse LIKE
operator, one that takes the pattern on the left".

You can brute-force this in a couple of lines with a SQL function
and a CREATE OPERATOR command.  But it won't be tremendously
efficient like that.  If performance is critical it'd be worth
writing the shim function in C.

I'm not sure why we've never got round to providing such a thing
in core ... probably lack of consensus on what to name the reverse
operator.  You'd need to support regex cases as well, so there's
more than one operator name to come up with.

            regards, tom lane