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/format ting.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.
I would be considering a trigger that populates a date column and a normal index on said date column.
David J.
