Re: Using POSIX Regular Expressions on xml type fields gives inconsistent results

"David Johnston" <polobo(at)yahoo(dot)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