函数索引引用的函数必须是immutable类型
2024-08-31 16:02:42
用户在使用中，可能会用到基于函数的索引，但是函数是非 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
最新文章
- python计算器
- SharePoint 2013 对二进制大型对象(BLOB)进行爬网
- bzoj 1305 dance跳舞
- 图片懒加载插件lazyload使用方法
- hdu1166敌兵布阵_线段树单点更新
- (easy)LeetCode 242.Valid Anagram
- Hbse的读写过程
- HTML5 文件域+FileReader 读取文件(一)
- mysql导入excel数据
- RPC 实现
- Maven命令行创建web项目，并部署到jobss当中(解决No plugin found for prefix 'jboss-as' in the current project and in the plugin groups [org.apache.maven.plugins,问题)
- BZOJ3224/洛谷P3391 - 普通平衡树(Splay)
- no system images installed for this target这个问题如何解决？
- [Swift]LeetCode961. 重复 N 次的元素 | N-Repeated Element in Size 2N Array
- js中提示框闪退问题
- Windows安装diango框架<一>
- PHP json_encode函数中需要注意的地方
- 46. 47. Permutations and Permutations II 都适用（Java，字典序 + 非字典序排列）
- 【转】Linux 如何通过命令仅获取IP地址
- vsphere中虚机的cpu热插拔和内存热添加