POSIX Regular Expressions on xml type fields

From: Denis Papathanasiou <denis(dot)papathanasiou(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: POSIX Regular Expressions on xml type fields
Date: 2012-12-20 20:55:55
Message-ID: CAEpnaGzL29NXbWvUZt6A=bOx+8wH5Z+nmr0qL98gZKCp_HRgAA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

and

=> select id from form_d where '(kumar|gonzales)' ~* any(
CAST(xpath('//PersonsList/PersonName/LastName/text()', data) as
TEXT[]) );
id
--------------------------------------
aea32e7e-f422-405c-953b-86fe3c8c1e30
97e1541b-27f4-4d95-beb5-2f67830ebc48
(2 rows)

I.e., they are successful in that the last names in the xml data are
"Kumar" and "Gonzales", so the ~* operator handled the case comparison
correctly, and the (|) grouping also found the two rows where the
corresponding xml had "Kumar" and "Gonzales" in the PersonList
attribute.

But if I change the expression to ask for all last names beginning
with "Kuma" or "Gonza", like this, the query returns no matches:

=> select id from form_d where '^kuma' ~* any(
CAST(xpath('//PersonsList/PersonName/LastName/text()', data) as
TEXT[]) );
id
----
(0 rows)

=> select id from form_d where '^gonza' ~* any(
CAST(xpath('//PersonsList/PersonName/LastName/text()', data) as
TEXT[]) );
id
----
(0 rows)

=> select id from form_d where '^(kuma|gonza)' ~* any(
CAST(xpath('//PersonsList/PersonName/LastName/text()', data) as
TEXT[]) );
id
----
(0 rows)

Why is that?

Browse pgsql-general by date

  From Date Subject
Next Message Alan Nilsson 2012-12-20 22:08:19 Log rolling
Previous Message jg 2012-12-20 20:26:17 Heavy function optimisation ?