函数索引引用的函数必须是immutable类型

用户在使用中，可能会用到基于函数的索引，但是函数是非 immutable 类型的，导致函数索引无法创建。如：

?

1 2	test=# create index ind_t1 on t1(to_char(create_date,'yyyy-mm')); ERROR:  functions in index expression must be marked IMMUTABLE

这里我们先看下函数的类型：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

test=# \df+ to_char                                                                                                              List of functions    Schema   |  Name   | Result data type |        Argument data types        | Type | Volatility | Parallel | Owner  | Security | Access privileges | Language |           Source code           |               Description               ------------+---------+------------------+-----------------------------------+------+------------+----------+--------+----------+-------------------+----------+---------------------------------+-----------------------------------------  pg_catalog | to_char | text             | bigint, text                      | func | stable     | safe     | system | invoker  |                   | internal | int8_to_char                    | format int8 to text  pg_catalog | to_char | text             | double precision, text            | func | stable     | safe     | system | invoker  |                   | internal | float8_to_char                  | format float8 to text  pg_catalog | to_char | text             | integer, text                     | func | stable     | safe     | system | invoker  |                   | internal | int4_to_char                    | format int4 to text  pg_catalog | to_char | text             | interval, text                    | func | stable     | safe     | system | invoker  |                   | internal | interval_to_char                | format interval to text  pg_catalog | to_char | text             | numeric, text                     | func | stable     | safe     | system | invoker  |                   | internal | numeric_to_char                 | format numeric to text  pg_catalog | to_char | text             | real, text                        | func | stable     | safe     | system | invoker  |                   | internal | float4_to_char                  | format float4 to text  pg_catalog | to_char | text             | timestamp without time zone, text | func | stable     | safe     | system | invoker  |                   | internal | timestamp_to_char               | format timestamp to text  pg_catalog | to_char | text             | timestamp with time zone, text    | func | stable     | safe     | system | invoker  |                   | internal | timestamptz_to_char             | format timestamp with time zone to text  pg_catalog | to_char | text             | tinyint                           | func | immutable  | safe     | system | invoker  |                   | sql      | select cast($1 as text)         | convert tinyint to text  pg_catalog | to_char | text             | tinyint, text                     | func | stable     | safe     | system | invoker  |                   | c        | tinyint_to_char                 | format tinyint to text  sys        | to_char | text             | bigint                            | func | immutable  | safe     | system | invoker  |                   | sql      | select to_char($1::text);       |  sys        | to_char | text             | boolean                           | func | stable     | unsafe   | system | invoker  |                   | sql      |  select cast($1 as text);      +|             |         |                  |                                   |      |            |          |        |          |                   |          |                                 |  sys        | to_char | text             | boolean, text                     | func | stable     | unsafe   | system | invoker  |                   | sql      |  select text($1);              +|             |         |                  |                                   |      |            |          |        |          |                   |          |                                 |  sys        | to_char | text             | integer                           | func | immutable  | safe     | system | invoker  |                   | sql      | select to_char($1::text);       |  sys        | to_char | text             | smallint                          | func | immutable  | safe     | system | invoker  |                   | sql      | select to_char($1::text);       |  sys        | to_char | text             | text                              | func | immutable  | safe     | system | invoker  |                   | plpgsql  |                                +|             |         |                  |                                   |      |            |          |        |          |                   |          | begin                          +|             |         |                  |                                   |      |            |          |        |          |                   |          |           return $1;           +|             |         |                  |                                   |      |            |          |        |          |                   |          | end;                            |  sys        | to_char | text             | text, text                        | func | immutable  | safe     | system | invoker  |                   | sql      | select to_char($1::numeric,$2); |  sys        | to_char | text             | timestamp without time zone       | func | immutable  | safe     | system | invoker  |                   | c        | ora_to_char_timestamp           | Convert timestamp to string (18 rows)

我们在看下对象的数据类型：

?

1 2 3 4 5 6

test=# \d t1                    Table "public.t1"    Column    |  Type   | Collation | Nullable | Default -------------+---------+-----------+----------+---------  id          | integer |           |          |  create_date | date    |           |          |

可以看到对象的 类型是date，再根据date类型寻找具体的to_char 函数，并修改为immutable

?

1 2	test=# alter function to_char(timestamp without time zone, text) immutable; ALTER FUNCTION

再创建索引：

?

1 2	test=# create index ind_t1 on t1(to_char(create_date,'yyyy-mm'));          CREATE INDEX