Re: BUG #11211: regexp_matches acts like a WHERE

mail@eduard-wulff.de writes:
>  SELECT vorgangt.id,
>   (regexp_matches(vorgangt.text, '#\w{5,26}', 's'))[1] AS sernr,
>     regexp_replace(vorgangt.text, '\n', '⁋', 'g') AS text_in_einer_zeile
>  FROM vorgangt ORDER BY 1

> This works to get the first match if there is one.

> BUT: it also _eliminates_ all rows that do not match from the result set

I see no bug here.  You've got a set-returning function in the target
list, and when it returns zero rows, you get zero rows (from that source
row).  You seem to wish it would return a scalar NULL for no match,
but that's not how the function is defined.

You could do it like this instead:

 SELECT vorgangt.id,
  (SELECT regexp_matches(vorgangt.text, '#\w{5,26}', 's'))[1] AS sernr,
    regexp_replace(vorgangt.text, '\n', '⁋', 'g') AS text_in_einer_zeile
 FROM vorgangt ORDER BY 1

Or you could wrap regexp_matches in a non-set-returning function.

            regards, tom lane