Postgres Full Text Search

full_text_search.sql

CREATE TABLE messages (
    title       text,
    body        text,
    tsv         tsvector
);

CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON messages FOR EACH ROW EXECUTE PROCEDURE
tsvector_update_trigger(tsv, 'english', title, body);

INSERT INTO messages VALUES('title here', 'the body text is here');


--- using a custom trigger and set weight on columns

CREATE FUNCTION messages_search_trigger() RETURNS trigger AS $$
begin
new.searchable_column :=
  setweight(to_tsvector('english', coalesce(new.title,'')), 'A') ||
  setweight(to_tsvector('english', coalesce(new.body,'')), 'D');
return new;
end
$$ LANGUAGE plpgsql;


CREATE TRIGGER search_update_on_messages BEFORE INSERT OR UPDATE
ON messages FOR EACH ROW EXECUTE PROCEDURE messages_search_trigger();