RES: Trouble with regexp_matches

> -----Mensagem original-----
> De: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
> Enviada em: sábado, 5 de novembro de 2016 15:13
> Para: Edson Richter <edsonrichter(at)hotmail(dot)com>; pgsql-
> general(at)postgresql(dot)org
> Assunto: Re: [GENERAL] Trouble with regexp_matches
>
> On 11/05/2016 10:01 AM, Edson Richter wrote:
> > Dear list,
> >
> >
> >
> > Version string PostgreSQL 9.4.10 on x86_64-unknown-linux-gnu,
> > compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
> >
> >
> >
> > 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!!!
> >
> >
> >
> > Is this a collateral effect of using regexp_matches in columns?
> >
> > If yes, shall not this information be BOLD RED FLASHING in
> > documentation (or it is already, and some kind sould would point me
> where)?
>
> https://www.postgresql.org/docs/9.4/static/functions-matching.html

I see - I always believed that this page was related to WHERE clause or using functions in the PostgreSQL way (which is, in your turn, a alternative to "from" syntax) - not for the select clause.
But now that you mention it, and re-reading all the information, I can understand the implications.

Nevertheless, would be nice to put a huge warning at the "String functions" page about this behavior...

>
> "It is possible to force regexp_matches() to always return one row by using a
> sub-select; this is particularly useful in a SELECT target list when you want all
> rows returned, even non-matching ones:
>
> SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab; "
>

Thanks, this worked - as well removing the "regexp_matches" and using "SUBSTRING( text FROM pattern)".

I really appreciate your help.

Kind regards,

Edson Richter

>
>
> >
> >
> >
> > -- First query (that is limiting results)
> > ---------------------------------------------------------------------
> >
> > select codigoocorrencia, datahoraocorrencia, datahoraimportacao,
> > observacao, regexp_matches(observacao, '\d\d/\d\d/\d\d\d\d')
> >
> > from batchloteocorrencia
> >
> > where codigoocorrencia = '091'
> >
> > and observacao is not null
> >
> > order by datahoraimportacao DESC
> >
> >
> >
> > Total results = 59
> >
> >
> >
> > --Second query (that is not limiting results, as I did
> > expect)---------------------------------------------------------------
> > ----
> >
> > select codigoocorrencia, datahoraocorrencia, datahoraimportacao,
> > observacao
> >
> > from batchloteocorrencia
> >
> > where codigoocorrencia = '091'
> >
> > and observacao is not null
> >
> > order by datahoraimportacao DESC
> >
> >
> >
> > Total results = 3826
> >
> >
> >
> >
> >
> > Why is that?
> >
> >
> >
> > Regards,
> >
> >
> >
> > Edson Richter
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com