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

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

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

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

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)

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

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

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

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

再创建索引：

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