Thanks Tom,
your sql worked as I intended it - now I even read it in the documentat=
ion=20
(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=20
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-WHE=
RE-clause-td5733684.html
Regards,
Eduard
Am Dienstag, 19. August 2014, 18:20:03 schrieb Tom Lane:
> mail@eduard-wulff.de writes:
> > SELECT vorgangt.id,
> > =20
> > (regexp_matches(vorgangt.text, '#\w{5,26}', 's'))[1] AS sernr,
> > =20
> > regexp_replace(vorgangt.text, '\n', '=E2=81=8B', 'g') AS text_i=
n_einer_zeile
> > =20
> > FROM vorgangt ORDER BY 1
> >=20
> > This works to get the first match if there is one.
> >=20
> > BUT: it also _eliminates_ all rows that do not match from the resul=
t set
>=20
> 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 sou=
rce
> row). You seem to wish it would return a scalar NULL for no match,
> but that's not how the function is defined.
>=20
> You could do it like this instead:
>=20
> SELECT vorgangt.id,
> (SELECT regexp_matches(vorgangt.text, '#\w{5,26}', 's'))[1] AS sern=
r,
> regexp_replace(vorgangt.text, '\n', '=E2=81=8B', 'g') AS text_in_=
einer_zeile
> FROM vorgangt ORDER BY 1
>=20
> Or you could wrap regexp_matches in a non-set-returning function.
>=20
> =09=09=09regards, tom lane