From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Edson Richter <edsonrichter(at)hotmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: RES: Trouble with regexp_matches |
Date: | 2016-11-05 19:43:12 |
Message-ID: | e473c5be-eced-dc77-7722-754b8f7623b3@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 11/05/2016 11:56 AM, Edson Richter wrote:
>> -----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...
Well the above page is pointed to in the string functions section:
https://www.postgresql.org/docs/9.4/static/functions-string.html
"regexp_matches(string text, pattern text [, flags text])
setof text[] Return all captured substrings resulting from matching a
POSIX regular expression against the string. See Section 9.7.3 for more
information."
Where 'Section 9.7.3' is a link to it. Not sure if that counts as a
warning though:)
>
>>
>> "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
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Joseph Krogh | 2016-11-06 11:34:10 | Parallel pg_dump/restore and LOs |
Previous Message | Edson Richter | 2016-11-05 18:56:01 | RES: Trouble with regexp_matches |