PostgreSQL full-text search with arrays

In Postgres 9.6 array_to_tsvector was added.

If you are dealing with same table you can write it something like this.

CREATE FUNCTION tsv_trigger() RETURNS trigger AS $$ begin IF (TG_OP = 'INSERT') OR old.array_column <> new.array_column THEN new.tsv := array_to_tsvector( new.array_column); END IF; return new; end $$ LANGUAGE plpgsql; CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON my_table FOR EACH ROW EXECUTE PROCEDURE tsv_trigger(); 

If you are dealing with two tables than you need to write update

CREATE FUNCTION cross_tables_tsv_trigger() RETURNS trigger AS $$ begin IF (TG_OP = 'INSERT') OR old.array_column <> new.array_column THEN UPDATE search_table st SET tsv = array_to_tsvector( new.array_column ) WHERE st.id = new.searchable_record_id END IF; # you can't return NULL because you'll break the chain return new; end $$ LANGUAGE plpgsql; 

Pay attention that it will differ from default to_tsvector( array_to_string() ) combination.

It goes without position numbers, and lowercase normalization so you can get a unexpected results.