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

On Mon, May 31, 2010 at 10:11 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Robert Haas wrote:
>> On Sat, May 29, 2010 at 5:00 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> > 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).
>>
>> I don't find this part to be something we should include in the
>> documentation.  If we want to include a workaround, how about defining
>> a non-SRF that just calls the SRF and returns the first row?
>
> Remember this has to return one row for no matches, so a simple SRF will
> not work.  I also have not seen enough demand for another function.  A
> single doc mention seemed the appropriate level of detail for this.

Well, we can debate later whether to add another function to core, but
what I meant was that the user having the problem could create a
user-defined function that calls regexp_matches() and returns the
first row, or NULL.

But actually here's an even simpler workaround, which is IMHO less
ugly than the original one:

SELECT foo, bar, (SELECT regexp_matches(bar, pattern)) FROM table;

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company