Tom,
Thanks for the reply. Here's the function:
create or replace function bnoz(date, int)
returns text as '
begin return $1 || '' '' || $2;
end; '
language 'plpgsql' immutable;
But now I'm embarrassed. When I load this function into a new invocation
of psql, the CREATE INDEX call works fine. I must have missed an error
message like:
sysd=> create index bnoz_idx on scenario_evidence (bnoz(alert_date,
period));
ERROR: relation "bnoz_idx" already exists
from an old attempt to create the index.
As you say though, for safety's sake a function like this should take
account of the DateStyle in use, and stick with it.
I guess I was just disconcerted that an example so close to the one in
the doc failed.
Sorry, and thanks for the advice, Rich C.
Tom Lane wrote:
> Rich Cullingford <rculling@sysd.com> writes:
>
>>sysd=> create index bnoz_idx on evidence (bnoz(alert_date, period));
>>ERROR: functions in index expression must be marked IMMUTABLE
>
>
> How is bnoz declared, exactly? You did not show us the function
> signature.
>
>
>>sysd=> create index bnoz_idx on evidence ((alert_date || ' ' || period));
>>ERROR: functions in index expression must be marked IMMUTABLE
>
>
> Assuming alert_date is of type DATE, this would fail because the
> date-to-text coercion function is not immutable (for the simple reason
> that its results depend on the DateStyle variable as well as the input
> value).
>
> I am guessing that bnoz is declared to take type text as its first
> argument, which means that the above index declaration includes an
> implicit date-to-text coercion as part of the index expression,
> which quite rightly causes the CREATE INDEX to fail. You'd not
> want your index to break if you changed DateStyle.
>
> When I tried to duplicate this, I declared bnoz as taking type DATE,
> and the CREATE INDEX worked just fine. However, I then had a time
> bomb on my hands, because the index entries in fact depended on
> the setting of DateStyle --- the internal conversion occurring inside
> bnoz isn't immutable, and so I was lying to claim that bnoz was.
>
> The safe way to approach this would be to declare bnoz to take date,
> and be careful to do the text conversion inside it in a
> DateStyle-independent manner, perhaps using to_char(). Better watch out
> for TimeZone dependencies, too. I think you'd need to write something
> like
> to_char($1::timestamp without time zone, 'YYYY/MM/DD')
> to be sure about that. (Of course you can pick any date format you
> like here, you just can't change your mind without rebuilding the
> index.)
>
> regards, tom lane
