> -----Mensagem original----- > De: Tom Lane [mailto:tgl@xxxxxxxxxxxxx] > Enviada em: sábado, 5 de novembro de 2016 15:21 > Para: Edson Richter <edsonrichter@xxxxxxxxxxx> > Cc: pgsql-general@xxxxxxxxxxxxxx > Assunto: Re: Trouble with regexp_matches > > Edson Richter <edsonrichter@xxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
