![]() Setweight(to_tsvector('english', title), 'A') || ![]() ![]() The vector should also have a dictionary specified, in this case 'english'. The key part here is the setweight function that takes a vector and a weight of A, B, C or D for priority. Setweight(to_tsvector('pg_catalog.english', CASE WHEN new.presentation THEN 'presentation' ELSE '' END),'B') Įxecute "CREATE TRIGGER article_search_updateīEFORE INSERT OR UPDATE OF title, introduction, main_body, tags, presentationįOR EACH ROW EXECUTE PROCEDURE article_search_trigger()" Setweight(to_tsvector('pg_catalog.english', coalesce(new.main_body,'')), 'D') || Setweight(to_tsvector('pg_catalog.english', coalesce(new.introduction,'')), 'D') || Setweight(to_tsvector('pg_catalog.english', coalesce(immutable_array_to_string(new.tags,' '))), 'B') || Setweight(to_tsvector('pg_catalog.english', coalesce(new.title,'')), 'A') || ![]() Here's the result in the migration.Įxecute "CREATE OR REPLACE FUNCTION immutable_array_to_string(arr ANYARRAY, sep TEXT)Įxecute "CREATE OR REPLACE FUNCTION article_search_trigger() RETURNS trigger AS $$ The search index needs an immutable string so we're going to create an additional immutable_array_to_string function that I pulled from Stack Overflow. We can do that with the array_to_string function.but that function returns a mutable string. I also have a boolean field to indicate presentations that I've given and if somebody searches for "presentation", I'd like that field to be given more weight.Īs an added twist my tags column is a postgres array which we need to convert to a string to include in our search. If I include a word or term in the title or tag that's probably more important than if it just happens to be included in the body of the text so I want to weight the fields in the search_vector. Now, my search includes 4 different fields: title, introduction, main_body and tags. WHERE search_vector to_tsquery('english','postgres') Which will allow us to just compare against the column knowing the proper index will be used once we have some data in there, like so. Ecto doesn't have anything built in for every piece of PostgreSQL functionality like GIN indexes, so for that we fall back to raw SQL with execute.Įxecute "CREATE INDEX article_search_index ON articles USING GIN(search_vector)" In order to solve the complexity problem and remove any ambiguity, we create a tsvector column with our migration and set an index on that column. If you're including multiple columns and potentially weighting them for priority it's going to get out of hand fast. If you're only including a single column in your search, that's probably not a big deal. Now when I include the above statement in the where clause of a query it will use the index but the downside is that I have to pass that huge chunk into the query every single time for it to get picked up. USING GIN (to_tsvector('english', title || ' ' || intro || ' ' || main_body)) You can also create an index with the result of the tsvector like this. That requires running the tsvector function on every row in a sequential scan. WHERE to_tsvector('english', title || ' ' || intro || ' ' || main_body) to_tsquery('english', 'postgres') When we run our search we compare a tsquery to a tsvector, which you can do on the fly like this. The documentation itself provides excellent step by step instructions but here is how we setup ours. In the interest of being thorough let's cover the entire setup process for PostgreSQL full text search. In our last article I skimmed over the details of setting up PostgreSQL search with Ecto. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |