> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Denis Papathanasiou
> Sent: Thursday, December 20, 2012 7:56 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Using POSIX Regular Expressions on xml type fields
gives
> inconsistent results
>
> I have a table with an xml type column, and while I can make regex queries
> like this successfully:
>
> => select id from form_d where 'kumar' ~* any(
> CAST(xpath('//PersonsList/PersonName/LastName/text()', data) as TEXT[])
> );
> id
> --------------------------------------
> 97e1541b-27f4-4d95-beb5-2f67830ebc48
> (1 row)
For educational purposes:
SELECT 'kumar' ~* 'Kumar' -> TRUE (simplest possible)
SELECT 'kumar' ~* any ( ARRAY['Kumar','Gonzales']::text[] ) -> TRUE (ok,
arrays work)
[let's play with partial matching now that we know full/basic matching
works]
SELECT 'kuma' ~* 'Kumar' -> FALSE ???? (wait, if this is broken then
regression tests would had to have caught this...I must have something
wrong)
[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.]
If you come across a problem with basic features of any software it is
likely you are confused rather than the system providing inconsistent
results. Take the feature in question and make sure you understand the more
basic uses (while omitting as many moving parts as possible - in this case
the use of XML when all you care about is how ANY(array) interacts with the
regular expression operator) and slowly building up until something breaks.
Shooting out a question to the mailing list is probably easier (for you) but
you are more likely to learn and remember if you figure it out yourself.
You had a good beginning with your original e-mail but you really needed to
take it a step or two further. The community is very helpful and forgiving
to these kinds of questions but it is in your own interest to dig deeper
before asking for help. This applies especially for basic and long-present
features such as regular expressions and arrays. And if it really is a bug
you will be able to provide some very helpful information in your report.
David J.
