> -----Mensagem original-----
> De: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Enviada em: sábado, 5 de novembro de 2016 15:21
> Para: Edson Richter <edsonrichter@hotmail.com>
> Cc: pgsql-general@postgresql.org
> Assunto: Re: [GENERAL] Trouble with regexp_matches
>
> Edson Richter <edsonrichter@hotmail.com> writes:
> > I’m running the query below, and it is limiting results as if
> “regexp_matches” being in where clause.
> > IMHO, it is wrong: in case there is no match, shall return null or empty array
> – not remove the result from the set!!!
>
> Well, no, because regexp_matches() returns a set. If there's no match,
> there's zero rows in the set.
For me, it is a strange behavior - putting something in select clause will restrict results as if it lies in join or
whereclauses.
>
> The standard workaround is to use a scalar sub-select, which has the effect
> of converting a zero-row result into a NULL:
>
> select codigoocorrencia, datahoraocorrencia, datahoraimportacao,
> observacao, (select regexp_matches(observacao, '\d\d/\d\d/\d\d\d\d'))
> from ...
>
> As of v10 there will be a less confusing solution: use regexp_match() instead.
>
> regards, tom lane
Ok, for now, I've changed regexp_matches() to "... substring(observacao from '\d\d/\d\d/\d\d\d\d')" which does the job
gracefully.
I still believe that an alert shall be made in the docs page (String functions), because seems confusing and error
prone.
Thanks,
Edson Richter
