> -----Mensagem original----- > De: Adrian Klaver [mailto:adrian.klaver@xxxxxxxxxxx] > Enviada em: sábado, 5 de novembro de 2016 15:13 > Para: Edson Richter <edsonrichter@xxxxxxxxxxx>; pgsql- > general@xxxxxxxxxxxxxx > Assunto: Re: 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.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
