CODEWITHSUNDEEP
sqlpostgresqlfull-text-searchtsvector
harijay
harijay

Reputation: 11883

Postgresql create TRIGGER for a two column ts_vector using tsearch2

Hi I am trying to learn how to use Full text search in postgres using version 9.2.2.

Here is my database:
title : character varying(1024)
body  : text
body_title_tsv | tsvector

I then defined the body_title_tsv to be a ts_vector made up from body and title fields, with coalesce to handle nulls.

UPDATE pg_rocks_post SET body_title_tsv = to_tsvector( coalesce(body,'') || coalesce (title,''));

I want to know how to write a TRIGGER that handles updates on "title" and/or "body".

I know I can create a Trigger using syntax like this and the tsearch2 function.

    CREATE TRIGGER body_title_tsv_trig BEFORE UPDATE OR INSERT on pg_rocks_post 
FOR EACH ROW EXECUTE PROCEDURE tsearch2(body_title_tsv , title);

I can do the same with body instead of title.

My Question is how do I combine the two to update the body_title_tsv

Or do I have to learn how to write my own function that essentially runs the SQL for body_title_tsv when UPDATE occurs? .

I know the other way to address this issue is to create an INDEX. But I am trying to understand how to write a trigger and read examples that use tsearch2 in the PostgreSQL book by Kory and Susan Douglas.

Edit: I came across this function that does this . Its the "tsvector_update_trigger" function. I am still interested in knowing if there is a way to do this using tsearch2.

create trigger body_title_tsv_trig BEFORE UPDATE OR INSERT on pg_rocks_post FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(body_title_tsv,'pg_catalog.english',title,body);

Upvotes: 1

Views: 1102

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

I would go about this somewhat differently. It seems like you want a consistent search interface for your table, and the best way to do this is to design it in a function. You can then index the output of the function. Note the function does not hit the table, and only operates on tuple data, and this is why we can index its output safely.

 CREATE OR REPLACE FUNCTION tsv(pg_rocks_post) RETURNS tsvector LANGUAGE SQL AS
 $$
 SELECT tsvector(coalesce($1.title, '') || ' ' || coalesce($1.body, ''));
 $$;

Now I can:

 SELECT tsv(p) FROM pg_rocks_post p;

and it will just work. I can also use the following form which would be more useful in some cases:

 SELECT p.tsv FROM pg_rocks_post.p;

or

 SELECT * from pg_rocks_post p WHERE plainto_tsquery('fat cat') @@ p.tsv;

Note you cannot omit the table alias since p.tsv is converted to tsv(p) as a function call. This is nice object-like syntactic sugar that can make your life easier with PostgreSQL.

The next step is in indexing the output of the function:

  CREATE INDEX pg_rocks_post_tsv_idx ON pg_rocks_post(tsv(pg_rocks_post));

There we go. no trigger needed and you get the index on the derived data.

Upvotes: 1

Related Questions