"David Johnston" <polobo@yahoo.com> writes:
> [optionally go look at " ~* " in the documentation at this point; or just
> try a simple flip-flop of the expression]
> SELECT 'Kumar' ~* 'kuma' -> TRUE !!! (ah ha! I had the order reversed)
> SELECT any( ARRAY['Kumar','Gozales']::text ) ... wait...ANY doesn't work on
> the other side... :(
> [At this point I'd confirm or question why ANY hasn't been made to go both
> ways but also realize that I will have to approach this in a different way
> to achieve my goal.]
It's been awhile since I looked at the point in detail, but I seem to
recall that there are fundamental syntactic-ambiguity reasons why the
ANY/ALL part has to be on the righthand side of the comparison operator.
There's a much easier fix to this problem though, which is to invent a
"reverse ~" operator that does POSIX comparison with the pattern on the
left. The hardest part of doing that for yourself is choosing a name
for the reverse operator --- it just goes like
create function reverse_regexeq(text, text) returns bool as
'select $2 ~ $1' language sql strict immutable;
create operator ~~~ (procedure = reverse_regexeq,
leftarg = text, rightarg = text);
and similarly for the case-insensitive version, and there you go:
pattern ~~~ ANY (whatever) solves the problem.
Every so often we debate providing built-in operators like this,
but we never seem to get past the what-to-call-it part. Anyone
have a good color for that bikeshed?
regards, tom lane
