but the workaround is running well and get's rated as "IMMUTABLE" though
returning timestamp and interval...
So maybe the allowness for setting the function in the workaround to
immutable will be the bug?
--WORKAROUND START:
CREATE OR REPLACE FUNCTION ud_data_timestamp1(val TEXT) RETURNS
TIMESTAMP WITH TIME ZONE AS $$ BEGIN RETURN CAST($1 AS TIMESTAMP WITH TIME ZONE); END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE OR REPLACE FUNCTION ud_data_timestamp2(val TEXT) RETURNS
TIMESTAMP WITHOUT TIME ZONE AS $$ BEGIN RETURN CAST($1 AS TIMESTAMP WITHOUT TIME ZONE); END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE OR REPLACE FUNCTION ud_data_interval(val TEXT) RETURNS INTERVAL AS $$ BEGIN RETURN CAST($1 AS INTERVAL); END;
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE INDEX ud_data_DT_ZONE ON ud_data(ud_data_timestamp1(val)) WHERE
i_type IN(5,6) AND (i_param & 4)=0;
CREATE INDEX ud_data_DT_GMT ON ud_data(ud_data_timestamp2(val)) WHERE
i_type IN(5,6) AND (i_param & 4)=4;
CREATE INDEX ud_data_TIME ON ud_data(ud_data_interval(val)) WHERE i_type=10;
--WORKAROUND END
Am 09.02.2016 um 18:15 schrieb Tom Lane:
> kurt@kwnet.at writes:
>> CREATE TABLE ud_data (
>> val TEXT,
>> );
>> CREATE INDEX ud_data_FLOAT ON ud_data(CAST(val AS FLOAT)) WHERE i_type=3;
>> --accepted as valid.
>> CREATE INDEX ud_data_TIME ON ud_data(CAST(val AS INTERVAL)) WHERE
>> i_type=10;
>> --results in error "functions in index expression must be marked IMMUTABLE"
> This is not a bug. The cast from text to interval isn't immutable because
> its results may vary depending on the IntervalStyle setting.
>
>> --the same error when casting to TIMESTAMP (with or without time zone)
> Same, although it's DateStyle and/or TimeZone that affect this.
>
> regards, tom lane
