Using POSIX Regular Expressions on xml type fields gives inconsistent results

Поиск
Список
Период
Сортировка
От Denis Papathanasiou
Тема Using POSIX Regular Expressions on xml type fields gives inconsistent results
Дата
Msg-id 50D3B39B.20804@banrai.com
обсуждение
Ответы Re: Using POSIX Regular Expressions on xml type fields gives inconsistent results
Re: Using POSIX Regular Expressions on xml type fields gives inconsistent results
Список 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?


В списке pgsql-general по дате отправления: