Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE
| От | David G. Johnston |
|---|---|
| Тема | Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE |
| Дата | |
| Msg-id | CAKFQuwaBuMJub1wLywG_WkSu57LT+h4AjdxMTSKfcaDM5_POLQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: [GENERAL] ERROR: functions in index expression must be markedIMMUTABLE (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: