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

On Tue, Feb 28, 2017 at 8:59 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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=e552c8d20b61a08204906
> 8d2f8d776e35fef1179;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:
>

​Further on that reads:

"​* Parse the 'date_txt' according to 'fmt', return results as a struct
pg_tm
* and fractional seconds."

Which makes it sound like a pure text parsing routine that applies minimal
logic to the values that it is parsing. In fact, its doesn't even accept a
TZ/OF formatting codes that could be used to determine shift. to_date is
always to going to output a date value that reflects the literal input text
with "positions" determined by the input format code.

Per Tom Lane [1] while the current implementation is in fact immutable at
some point in the future we may wish to add additional environmental logic
which will require that it be marked STABLE.

1. https://www.postgresql.org/message-id/4177.1322537348@sss.pgh.pa.us

I would be considering a trigger that populates a date column and a normal
index on said date column.

David J.