Re: BUG #5469: regexp_matches() has poor behaviour and more poor documentation

Daniele Varrazzo wrote:
> "If there is no match to the pattern, the function returns no rows" is
> easily overlooked as "it returns null", or some other behaviour that
> don't change the returned set. The point is, because the function is
> listed in the string function, you would expect the function to
> manipulate text, not the dataset. The function as it is is not safe to
> be used in a construct
>
> SELECT foo, bar, regexp_matches(bar, pattern) FROM table;
>
> unless you really wanted:
>
> SELECT foo, bar, regexp_matches(bar, pattern) FROM table WHERE bar
> ~ pattern;
>
> otherwise you have to take measures to be able to deal with records in
> which the pattern is not matched, for example:
>
> SELECT foo, bar, regexp_matches(bar, pattern || '|') FROM table;
>
> the latter still doesn't work when bar is NULL: in this case the
> record is dropped anyway, so I don't think it can be proposed as
> general solution.
>
> The characteristics of returning a set of text[] is useful when the
> user wants all the matches, not only the first one: the behaviour is
> selected specifying the flag 'g' as third argument.
>
> >From this point of view, I hope it can be stated that in its current
> form the regexp_matches() has not the most optimal interface. Please
> accept my apology for the tone being too rude in my previous message.

I found the description in the documentation quite confusing also. I
have created the attached documention patch which is clearer about the
behavior of regexp_matches().

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com