2

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?

5
  • 1
    Just concatenate (with a space in between) all the elements in the array together and use that as your document. Commented Aug 26, 2016 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. Commented Aug 26, 2016 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. Commented Aug 26, 2016 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. Commented Aug 26, 2016 at 23:42
  • 2
    For joining arrays to text use array_to_string(anyarray, text [, text]) in Postgresql. For SQLA and triggers have a look here stackoverflow.com/questions/8929738/… Commented Aug 26, 2016 at 23:44

1 Answer 1

1

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.