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

I have updated the patch, attached, to clarify that this returns text
arrays, and that you can force it to always return one row using
COALESCE() and a '|' pattern (the later suggested by Daniele Varrazzo).

---------------------------------------------------------------------------

Bruce Momjian wrote:
> 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