> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Denis Papathanasiou > Sent: Thursday, December 20, 2012 7:56 PM > To: pgsql-general@xxxxxxxxxxxxxx > Subject: 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
