From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | mail(at)eduard-wulff(dot)de |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #11211: regexp_matches acts like a WHERE |
Date: | 2014-08-19 22:20:03 |
Message-ID: | 22166.1408486803@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-08-19 22:25:32 | Re: BUG #11207: empty path will segfault jsonb #> |
Previous Message | Kevin Grittner | 2014-08-19 13:57:53 | Re: BUG #11208: Refresh Materialized View Concurrently bug using user Postgres |