Re: BUG #11211: regexp_matches acts like a WHERE

Thanks Tom,

your sql worked as I intended it - now I even read it in the documentation
(9.7.3) as a "Tip". I expected a scalar NULL as you wrote.

I did not grok the difference(?) between array and set. I even wondered about
my "syntax-solution" ()[n].

I am not the only one being on the wrong road:
http://postgresql.1045698.n5.nabble.com/Using-regexp-matches-in-the-WHERE-clause-td5733684.html

Regards,

Eduard

Am Dienstag, 19. August 2014, 18:20:03 schrieb Tom Lane:
> mail(at)eduard-wulff(dot)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