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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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@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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs