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  ("David Johnston" <polobo@yahoo.com>)
Re: Using POSIX Regular Expressions on xml type fields gives inconsistent results  ("David Johnston" <polobo@yahoo.com>)
Список 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 по дате отправления:

Предыдущее
От: Rob Sargent
Дата:
Сообщение: Re: Implicit transaction not rolling back after error
Следующее
От: "David Johnston"
Дата:
Сообщение: Re: Using POSIX Regular Expressions on xml type fields gives inconsistent results