CREATE INDEX trgm_idx_video_tags ON videos USING gist ((array_to_string(tags, ', ')) gist_trgm_ops)
But on running this I get an error as:
ERROR: functions in index expression must be marked IMMUTABLE
How can I fix this?
wrap array_to_string with text[] argument into a function and mark it as immutable:
create function txt_array_to_string (text[]) returns text language sql immutable as $$ select array_to_string($1,', ') $$;
And then build your index on that function.
I don't think there are any caveats on this. Array_to_string is not immutable because it can work with dates and numbers, which can change with configuration settings, such as timezone. But when given text[] argument, I think it actually is immutable.
