| From: | Denis Papathanasiou <denis(dot)papathanasiou(at)banrai(dot)com> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Using POSIX Regular Expressions on xml type fields gives inconsistent results |
| Date: | 2012-12-21 00:55:55 |
| Message-ID: | 50D3B39B.20804@banrai.com |
| Views: | Whole Thread | Raw Message | 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?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David Johnston | 2012-12-21 01:14:23 | Re: Using POSIX Regular Expressions on xml type fields gives inconsistent results |
| Previous Message | Rob Sargent | 2012-12-20 23:40:12 | Re: Implicit transaction not rolling back after error |