今天在一个时间字段类型上使用to_char(timestamp without time zone, text)的方式创建函数索引出现报错:
bill=# create index idx_tt1 on tt1 (to_char(crt_time,'yyyymmddhh24'));
psql: ERROR: 42P17: functions in index expression must be marked IMMUTABLE
LOCATION: ComputeIndexAttrs, indexcmds.c:1642
报错的意思很明显:用来创建函数索引的函数必须是immutable类型。
查看to_char函数的稳定性:
bill=# select proname,provolatile,proargtypes from pg_proc where proname='to_char';
proname | provolatile | proargtypes
---------+-------------+-------------
to_char | s | 1700 25
to_char | s | 23 25
to_char | s | 20 25
to_char | s | 700 25
to_char | s | 701 25
to_char | s | 1186 25
to_char | s | 1184 25
to_char | s | 1114 25
(8 rows)
的确,to_char函数的稳定性是stable。那为什么创建函数索引的函数必须得是immutable类型的呢?这也很好理解,pg必须确保在任何情况下, 函数得到的结果和建立函数索引时得到的结果是一致的, 否则就会出现走索引检索到的和走全表扫描检索到的结果不一致。
那该怎么办呢?
方法一:
将to_char函数的稳定性修改成immutable,但是不建议使用这种方法。
bill=# alter function to_char(timestamp without time zone, text ) immutable;
ALTER FUNCTION
bill=# create index idx_tt1 on tt1 (to_char(crt_time,'yyyymmddhh24'));
CREATE INDEX
方法二:
自己新建个immutable的函数来替换to_char
bill=# CREATE OR REPLACE FUNCTION my_to_char(some_time timestamp)
bill-# RETURNS text
bill-# AS
bill-# $BODY$
bill$# select to_char($1, 'yyyy-mm-dd');
bill$# $BODY$
bill-# LANGUAGE sql
bill-# IMMUTABLE;
CREATE FUNCTION
方法三:
新增一列用来存储to_char的值,这样就不用创建函数索引了。