Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE

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.klaver@aklaver.com