Re: ERROR: functions in index expression must be marked IMMUTABLE

On 27.02.2017 12:10, Geoff Winkless wrote:
> On 27 February 2017 at 10:52, Sven R. Kunze <srkunze(at)mail(dot)de
> <mailto:srkunze(at)mail(dot)de>>wrote:
>
>
> So, what can I do to parse texts to date(times) in a safe manner?
>
>
> You know best the format of your data; if you know that your date
> field is always in a particular style and timezone, you can write a
> function that can be considered safe to set IMMUTABLE, where a more
> generic​ system todate function cannot.
>
> It might be sensible to call the function something that describes it
> exactly, rather than my_to_date you could call it utc_yyyymmdd_todate
> or something, just in case someone comes along later and sees an
> immutable todate function and thinks they can use it for something else.
>
> Geoff

Thanks, Geoff. It's not the most convenient way to define an index to
define a function first and that all future queries need to use that
very function in order to utilize the index. Though, it's the most
safest and best documenting way.

So, I got two additional questions:

Why is this relevant for dates? I cannot see that dates are
timezone-influenced.

I still feel that a function is overkill for a simple text to date
conversion. Couldn't there be an IMMUTABLE modifier for an expression to
mark it as immutable?

"SELECT '2007-02-02'::date;" just works. It would be great if one could
define an index with the same ease. I already can see how our
application developers need constant reminders that "in case of dates,
use 'magic_function' first". If they don't, the application will suffer
from bad performance.

Thanks in advance for your replies.

Regards,
Sven