Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"David Johnston" <polobo@xxxxxxxxx> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux