Indexing an array for full text search

While most casts to text from basic types are defined IMMUTABLE, this it is not the case for array types. Quoting Tom Lane in a post to pgsql-general):

Because it's implemented via array_out/array_in rather than any more direct method, and those are marked stable because they potentially invoke non-immutable element I/O functions.

Bold emphasis mine.

We can work with that. The general case cannot be marked as IMMUTABLE. But for the case at hand (cast citext[] or text[] to text) we can safely assume immutability. Create a simple IMMUTABLE SQL function that wraps the function. You might as well wrap array_to_string() (like you already pondered) for which similar considerations apply.

For citext[] (create separate functions for text[] if needed):

Either based on a plain cast to text (faster):

CREATE OR REPLACE FUNCTION f_ciarr2text(citext[]) RETURNS text LANGUAGE sql IMMUTABLE AS 'SELECT $1::text'; 

Or using array_to_string() for a result without curly braces (a bit more correct):

CREATE OR REPLACE FUNCTION f_ciarr2text(citext[]) RETURNS text LANGUAGE sql IMMUTABLE AS $$SELECT array_to_string($1, ',')$$; 

Then:

CREATE INDEX doc_search_idx ON documents USING gin ( to_tsvector('english', COALESCE(f_ciarr2text(tags), '') || ' ' || COALESCE(notes,''))); 

I did not use the polymorphic type ANYARRAY like in your answer, because I know text[] or citext[] are safe, but I can't vouch for all array types.

Tested in Postgres 9.4 and works for me.

I added a space between the two strings to avoid false positive matches across concatenated strings. There is an example in the manual.

If you sometimes want to search just tags or just notes, consider a multicolumn index instead:

CREATE INDEX doc_search_idx ON documents USING gin ( to_tsvector('english', COALESCE(f_ciarr2text(tags), '') , to_tsvector('english', COALESCE(notes,'')); 

The risks you are referring to apply to temporal functions mostly, which are used in the referenced question. If time zones (or just the type timestamptz) are involved, results are not actually immutable. We do not lie about immutability here. Our functions are actually IMMUTABLE. Postgres just can't tell from the general implementation it uses.

Related

Often people think they need text search, while similarity search with trigram indexes would be a better fit:

• PostgreSQL LIKE query performance variations

Not relevant in this exact case, but while working with citext, consider this:

• Index on column with data type citext not used

array_to_tsvector() in Postgres 9.6+

This immutable function was added later:

• array_to_tsvector ( text[] ) → tsvector

  Converts an array of text strings to a tsvector. The given strings are used as lexemes as-is, without further processing. Array elements must not be empty strings or NULL.

It expects an array of lexemes, while this question has yet to run array elements through to_tsvector('english', $1) to get there. So not a direct replacement.

Here's my naive solution, to wrap it and call it immutable, as suspected.

 CREATE FUNCTION immutable_array_to_string(arr ANYARRAY, sep TEXT) RETURNS text AS $$ SELECT array_to_string(arr, sep); $$ LANGUAGE SQL IMMUTABLE ;