PostgreSQL 9.3
Use case: I have a blog tool that will allow users to create any number of blogs. Each blog could contain N number of
posts. When a user wishes to search for a post they only want to search within a particular blog.
I created the below view with the idea I would search against the field "document" for content within any particular
fk_blog_id. The challenge is how to build an appropriate index. Ideally the index would be fk_blog_id and then
document. Any help you could give me would be greatly appreciated.
CREATE MATERIALIZED VIEW blog.post_search AS
SELECT post.fk_blog_id,
post.id as fk_post_id,
setweight(to_tsvector('simple', post.title), 'A')
|| setweight(to_tsvector('simple', post.summary || post.article), 'B')
|| setweight(to_tsvector('simple', coalesce(string_agg(tag.name, ' '))), 'C') as document
FROM blog.post
JOIN blog.tag ON tag.fk_post_id = post.id
WHERE post.is_deleted=false
AND post.is_published=true
AND post.is_post=true
AND ( (post.article_type='article') OR (post.is_summary_enabled=true) )
GROUP BY post.fk_blog_id,post.id;
Thanks,
Lance Campbell
Software Architect
Web Services at Public Affairs
217-333-0382