oracle迁移过来的函数表达式索引出现下列问题,可以尝试用下列办法解决

迁移时需要保证创建的函数本身的属性,输入固定的值返回值不能变.

demo:

create table TABLE1(id int);

CREATE  INDEX X_IDX ON TABLE1 ((TO_NUMBER(ID)));

ERROR:  functions in index expression must be marked IMMUTABLE

解决办法:

CREATE OR REPLACE FUNCTION my_to_number(p_value text)

RETURNS number

AS

$$

SELECT to_number(p_value);

$$

LANGUAGE SQL IMMUTABLE STRICT;

 

CREATE INDEX INDEX ON table1 ((my_TO_NUMBER(ID)));