I would like to implement full-text search within my application but I'm running into some roadblocks associated with my Array-type columns. How would one implement a psql trigger so that the when my "object" table is updated, each element (which are strings) of its array column is added to the tsvector column of my "search" table?
1 Answer
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.
to_tsvector()just work on an array (aka apply itself to each element of the array)? Seems unlikely.array_to_string(anyarray, text [, text])in Postgresql. For SQLA and triggers have a look here stackoverflow.com/questions/8929738/…