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

On 02/28/2017 07:30 AM, Sven R. Kunze wrote:
> On 28.02.2017 15:40, Adrian Klaver wrote:
>> [explanation of why date casting and to_datetime don't work]
>
> Why is to_date not immutable?

Not sure, but if I where to hazard a guess, from the source code in
formatting.c:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/formatting.c;h=e552c8d20b61a082049068d2f8d776e35fef1179;hb=HEAD

to_date(PG_FUNCTION_ARGS)
{
text *date_txt = PG_GETARG_TEXT_P(0);
text *fmt = PG_GETARG_TEXT_P(1);
DateADT result;
struct pg_tm tm;
fsec_t fsec;

do_to_timestamp(date_txt, fmt, &tm, &fsec);
....

/*
* do_to_timestamp: shared code for to_timestamp and to_date

The shared code makes it not immutable:

test=> select * from pg_proc where proname ilike 'to_date';
...

provolatile | s

....

https://www.postgresql.org/docs/9.6/static/catalog-pg-proc.html

provolatile char

provolatile tells whether the function's result depends only on its
input arguments, or is affected by outside factors. It is i for
"immutable" functions, which always deliver the same result for the same
inputs. It is s for "stable" functions, whose results (for fixed inputs)
do not change within a scan. It is v for "volatile" functions, whose
results might change at any time. (Use v also for functions with
side-effects, so that calls to them cannot get optimized away.)

>
> Regards,
> Sven
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com