Generated column of tsvector for text array in Postgres (Supabase)

Question

in Postgres (Supabase) I am trying to automatically generate a column from another column which contains a text array of short title variants.

The tsvector works just fine and as expected. The other possibility which would be to use array_to_tsvector is not an option as short title text array contains not just single words but variants of short titles (sentences).

alter table "MOVIES"  
add column fts_short_title  
tsvector  GENERATED ALWAYS AS (  
to_tsvector('simple', 
array_to_string( title_short,' '::text)) 
 ) STORED;

but I get this error

Failed to run sql query: generation expression is not immutable

On the other hand I was successful when adding such a column for JSONB of full titles for different languages

alter table "MOVIES"  
add column fts  
tsvector  GENERATED ALWAYS AS ( 
to_tsvector('simple', 
coalesce(title->>'en', '') || ' ' ||  
coalesce(title->>'de', '') || ' ' ||  
coalesce(title->>'it', '') || ' ' ||  
coalesce(title->>'fr', ''))
  ) STORED;

Thank you very much for any tip and help. .. SQL is rather new to me, have used only from MongoDB previously, so sorry for my question.

Answer 1

You could define immutable wrappers for otherwise non-immutable functions. online demo

create or replace function array_to_string_immutable (
    arg text[], 
    separator text,
    null_string text default null) 
returns text immutable parallel safe language sql as $$
select array_to_string(arg,separator,null_string) $$;

alter table "MOVIES"  
add column fts_short_title  
tsvector  GENERATED ALWAYS AS (  
to_tsvector('simple', 
array_to_string_immutable( title_short,' '::text)) 
 ) STORED;
table "MOVIES";

While the textcat() function behind || operator is immutable, I'm pretty sure array_to_string() is only stable for the same reason concat() is so you need to be reasonably careful with where you use this workaround.

You could do the same for the other column to use a concat_ws() and avoid the repeated ||' '||coalesce():

create or replace function concat_ws_immutable (
    separator text,
    variadic arg text[]) 
returns text immutable parallel safe language sql as $$
select concat_ws(separator,variadic arg) $$;

alter table "MOVIES"  
add column fts  
tsvector  GENERATED ALWAYS AS ( 
 to_tsvector('simple',concat_ws_immutable(' ',title->>'en',title->>'de',title->>'it',title->>'fr'))
) STORED;

You are also free to do pretty much whatever you want, however you want to the column in a plpgsql function used by a trigger after insert or update on "MOVIES".