Please note...
On Mon, May 4, 2015 at 9:56 AM, Jeff Certain <gcertain@dynamicsignal.com>
wrote:
> Repro:
>
> drop table if exists regex_test;
>
> create table regex_test(filter citext);
>
> insert into regex_test(filter) values ('Filter Filter Filter');
>
select filter, regexp_matches(filter, 'Filter', 'g') from regex_test;
>
=E2=80=8B"filter" is of type "citext"=E2=80=8B
select filter, regexp_matches('Filter Filter Filter', 'Filter', 'g') from
> regex_test;
>
>
>
=E2=80=8B'Filter Filter Filter' is of type "text"=E2=80=8B
Expected result:
>
> Both select statements should return the same number of rows.
> Specifically, in this case, I expect to get back 3 rows.
>
>
>
=E2=80=8BThe following non-citext version of your first query works just fi=
ne on 9.3
=E2=80=8Bselect filter, regexp_matches(filter, 'Filter', 'g') from (values =
('Filter
Filter Filter'::text)) regex_test (filter);
=E2=80=8Bregexp_matches is not mentioned on the following page in the docum=
entation:
=E2=80=8Bhttp://www.postgresql.org/docs/9.4/interactive/citext.html
> Actual result:
>
> When referencing the filter column, only one row is ever returned. When
> supplying the string that is the same as the data in the filter column, t=
he
> correct number of rows are returned.
>
>
>
> Result 1 (incorrect):
>
> select filter, regexp_matches(filter, 'Filter', 'g') from regex_test;
>
> Filter Filter Filter {Filter}
>
>
>
> Result 2 (correct):
>
> select filter, regexp_matches('Filter Filter Filter', 'Filter', 'g') from
> regex_test;
>
> Filter Filter Filter {Filter}
>
> Filter Filter Filter {Filter}
>
> Filter Filter Filter {Filter}
>
=E2=80=8BThis fails on 9.3 if the citext type is used instead of text.
Seems the however the citext version of regexp_matches is invoked it
ignores the global flag. Haven't explored deeper.
David J.
=E2=80=8B