PostgreSQL full-text search with arrays

Question

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?

Just concatenate (with a space in between) all the elements in the array together and use that as your document. — univerio, Aug 26 '16 at 22:38
@univerio Right, I understand in theory. But, how would I actually execute this as SQL within my trigger. I know how to update my tsvector column when the columns it is being derived from are text. But how do I extract the individual elements of the array (within the SQL being emitted to create the trigger). Does `to_tsvector()` just work on an array (aka apply itself to each element of the array)? Seems unlikely. — Drew Burnett, Aug 26 '16 at 22:48
How is this SQLAlchemy related? What I mean is that though you could create SQLA event handlers, this is probably better served by SQL triggers in Postgresql. — Ilja Everilä, Aug 26 '16 at 23:39
@IljaEverilä I suppose only in the sense that there may be a way to create triggers via sqlalchemy which I'm currently unaware of. I'm using sqlalchemy and its declarative base class to map my tables into PostgreSQL. — Drew Burnett, Aug 26 '16 at 23:42
For joining arrays to text use [`array_to_string(anyarray, text [, text])`](https://www.postgresql.org/docs/current/static/functions-array.html) in Postgresql. For SQLA and triggers have a look here http://stackoverflow.com/questions/8929738/sqlalchemy-declarative-defining-triggers-and-indexes-postgres-9 — Ilja Everilä, Aug 26 '16 at 23:44

Алексей Лещук · Answer 1 · 2017-07-06T09:40:03.730

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.

PostgreSQL full-text search with arrays

1 Answers1