Re: filter records by substring match of an postgresql array column

On Wed, Nov 8, 2017 at 4:28 AM, Arup Rakshit <aruprakshit1987(at)outlook(dot)com>
wrote:

>
> And to do I wanted to add an index like:
>
> 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.

Cheers,

Jeff