Postgres- "ERROR: generation expression is not immutable" why is my expression not immutable?

Question

I am trying to create an index for text search in Postgres, but I keep getting an error when I create generated tsvector column.

ERROR:  generation expression is not immutable
SQL state: 42P17

I have a text "title" column and text[] "authors" column. I am trying to combine the two to create a tsvector column

Here is the code that's getting the error

ALTER TABLE book
    ADD COLUMN tscol tsvector
        GENERATED ALWAYS AS (to_tsvector(title || ' ' || immutable_array_to_string(coalesce(authors, '{}'), ' '))) STORED;

Code for immutable_array_to_string function:

CREATE OR REPLACE FUNCTION immutable_array_to_string(text[], text) 
    RETURNS text as $$ SELECT array_to_string($1, $2); $$ 
LANGUAGE sql IMMUTABLE;

Keri

Answer 1

You are calling to_tsvector without a regconfig, so the default one is used. In such case, the function is only stable. If you want it to be immutable, you must pass the regconfig.

to_tsvector('english',title || ' ' || immutable_array_to_string(coalesce(authors, '{}'), ' ')) 

PS: you can call \df+ to_tsvector to see the volatility of the function with different signatures.

Brote

Thank you!!! I see my mistake now. Also thanks for \df+ to_tsvector tip :))) – Keri