How to create tsvector_update_trigger on Non-character type data in PostgreSQL with gin?

1
votes

How can I create a tsvector update trigger on non-character data type in PostgreSQL with gin for full-text search support?

For instance, having the case of a posts table that has:

  • title (varchar)
  • text (varchar)
  • status (enum ['published', 'draft']
  • likes (integer)

I've created an index column:

ALTER TABLE "posts" ADD COLUMN "posts_full_text" TSVECTOR';

UPDATE "posts" SET "posts_full_text" = to_tsvector('english', title || ' ' || text || ' ' || status || ' ' || likes);

CREATE INDEX np_search_idx ON "posts" USING gin("posts_full_text");

Then tried to create trigger to update:

CREATE TRIGGER update_posts_tsvector BEFORE INSERT OR UPDATE 
ON posts FOR EACH ROW EXECUTE PROCEDURE 
tsvector_update_trigger("posts", 'pg_catalog.english', title, text, status, likes);

The statement above throws an error saying that status and likes columns are non character types.

Trying to cast won't work neither throwing an error that says that method signature doesn't match:

CREATE TRIGGER update_posts_tsvector BEFORE INSERT OR UPDATE 
ON posts FOR EACH ROW EXECUTE PROCEDURE 
tsvector_update_trigger("posts", 'pg_catalog.english', title, text, status::VARCHAR, likes::VARCHAR);

So, by this way is impossible to index non char variant columns until a casting strategy is achieved.

1
postgresqlfull-text-search

1 Answers

1
votes

The way to achieve this is by creating a stored function where we can cast and then pass it to to_tsvector() instead to tsvector_update_trigger():

CREATE OR REPLACE FUNCTION update_posts_tsvector() RETURNS trigger AS $$
begin
  new."posts" :=
     setweight(to_tsvector('pg_catalog.english', coalesce(new.title,'')), 'A') ||
    setweight(to_tsvector('pg_catalog.english', coalesce(new.text,'')), 'B') ||
    setweight(to_tsvector('pg_catalog.english', coalesce(new.status::TEXT,'')), 'B') ||
    setweight(to_tsvector('pg_catalog.english', coalesce(new.likes::TEXT,'')), 'B');
  return new;
end
$$ LANGUAGE plpgsql;

(we can omit setweight wrapping method and just use to_tsvector directly)

And then bind that function to our updating trigger:

CREATE TRIGGER np_vector_update BEFORE INSERT OR UPDATE ON "posts" FOR EACH ROW EXECUTE PROCEDURE update_posts_tsvector();

Hope this will be useful for someone.