RES: Trouble with regexp_matches

> -----Mensagem original-----
> De: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Enviada em: sábado, 5 de novembro de 2016 15:21
> Para: Edson Richter <edsonrichter(at)hotmail(dot)com>
> Cc: pgsql-general(at)postgresql(dot)org
> Assunto: Re: [GENERAL] Trouble with regexp_matches
>
> Edson Richter <edsonrichter(at)hotmail(dot)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 where clauses.

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