1

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.

Kvetoslav
  • 443
  • 3
  • 15

1 Answers1

2

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".

Zegarek
  • 6,424
  • 1
  • 13
  • 24
  • You are brilliant, the function works like a charm!!!. Thank you very much for your complex answer. – Kvetoslav Feb 16 '23 at 16:59
  • I searched for related threads to see what the risks are exactly with this approach and unsurprisingly, this turns out to be a workaround about as old as [function volatility categories](https://www.postgresql.org/docs/current/xfunc-volatility.html) themselves, which comes with the benefit of being tested and improved along the way: you might want to read [this](https://stackoverflow.com/a/54384767/5298879) and [this](https://dba.stackexchange.com/a/164081/242866). I subsequently added `parallel safe` to the answer. – Zegarek Feb 16 '23 at 17:55
  • Strange but Postrges in Supabase gives me this error `Failed to run sql query: generation expression is not immutable` for concat_ws_immutable function. – Kvetoslav Feb 16 '23 at 18:36
  • I noticed that the second block of code in my answer defined the `concat_ws_immutable()` and then still used the regular, non-immutable `concat_ws()` in the generated expression. It's now corrected. – Zegarek Feb 17 '23 at 09:08
  • Not a problem, I have noticed that already @/Zegarek – Kvetoslav Feb 17 '23 at 09:41