Create a postgresql index in a text column cast from an array

Question

I have a postgresql table that has a column with data type = 'text' in which I need to create an index that includes this column of type casted to integer []. However, when I try to do this, I get the following error:

ERROR: functions in index expression must be marked IMMUTABLE

Here is the code:

create table test (a integer[], b text); insert into test values ('{10,20,30}','{40,50,60}'); CREATE INDEX index_test on test USING GIN (( b::integer[] )); 

Please note that one of the possible solutions to the problem is to create a function that is marked as IMMUTABLE, which takes a column value and performs type casting inside the function, but the problem (in addition to adding overhead) is that I have many different target "types data arrays (eg: text [], int2 [], int4 [], etc.), and it would be impossible to create a separate function for each data type of the potential target array.

Answer 1

Answered in this thread on the PostgreSQL mailing lists. Click "Follow-ups" or "next by thread" in the links after the message to follow the (short) thread in the thread.

There is no recipe, but Tom just talks about defining an explicit cast from text[] to integer[] . If time permits, I will answer this question with an example.