From: | Edson Richter <edsonrichter(at)hotmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | RES: Trouble with regexp_matches |
Date: | 2016-11-05 18:08:27 |
Message-ID: | CY1PR01MB1818E86AEB69B18A31BBCF01CFA50@CY1PR01MB1818.prod.exchangelabs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> -----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
From | Date | Subject | |
---|---|---|---|
Next Message | Edson Richter | 2016-11-05 18:56:01 | RES: Trouble with regexp_matches |
Previous Message | Tom Lane | 2016-11-05 17:21:07 | Re: Trouble with regexp_matches |