I am working on ts_vector update which will be called when a row updated or inserted. My working query is like;
UPDATE backend_article SET search_vector =
setweight(to_tsvector(coalesce(lookup.title,'')), 'A') ||
setweight(to_tsvector(coalesce(lookup.keywords,'')), 'B') ||
setweight(to_tsvector(coalesce(lookup.abstract,'')), 'B') ||
setweight(array_to_tsvector(lookup.authors_list), 'B') ||
setweight(array_to_tsvector(lookup.tag_label_list), 'B') ||
setweight(array_to_tsvector(lookup.tag_wiki_list), 'B') ||
setweight(array_to_tsvector(lookup.tag_description_list), 'B')
FROM
(SELECT backend_article.title as title, backend_article.keywords as keywords,
backend_article.abstract as abstract,
coalesce(array_agg(distinct backend_author.name) filter (where (backend_author.name is not null) and (length(backend_author.name) > 0)),'{}') as authors_list,
coalesce(array_agg(distinct backend_tag.label) filter (where (backend_tag.label is not null) and (length(backend_tag.label) > 0)), '{}') as tag_label_list ,
coalesce(array_agg(distinct backend_tag.wiki_description) filter (where (backend_tag.wiki_description is not null) and (length(backend_tag.wiki_description) > 0)), '{}') as tag_wiki_list,
coalesce(array_agg(distinct backend_tag.custom_description) filter (where (backend_tag.custom_description is not null) and (length(backend_tag.custom_description ) > 0)), '{}') as tag_description_list
FROM backend_article
LEFT OUTER JOIN backend_article_authors ON (backend_article.id = backend_article_authors.article_id)
LEFT OUTER JOIN backend_author ON (backend_article_authors.author_id = backend_author.id)
LEFT OUTER JOIN backend_article_tags ON (backend_article.id = backend_article_tags.article_id)
LEFT OUTER JOIN backend_tag ON (backend_article_tags.tag_id = backend_tag.id)
GROUP BY backend_article.id
ORDER BY backend_article.id ASC) as lookup
where backend_article.title = lookup.title
I tried create a function with this way;
CREATE FUNCTION search_vector_update() RETURNS trigger AS $$
begin
UPDATE backend_article SET search_vector =
setweight(to_tsvector(coalesce(lookup.title,'')), 'A') ||
setweight(to_tsvector(coalesce(lookup.keywords,'')), 'B') ||
setweight(to_tsvector(coalesce(lookup.abstract,'')), 'B') ||
setweight(array_to_tsvector(lookup.authors_list), 'B') ||
setweight(array_to_tsvector(lookup.tag_label_list), 'B') ||
setweight(array_to_tsvector(lookup.tag_wiki_list), 'B') ||
setweight(array_to_tsvector(lookup.tag_description_list), 'B')
FROM
(SELECT backend_article.title as title, backend_article.keywords as keywords,
backend_article.abstract as abstract,
coalesce(array_agg(distinct backend_author.name) filter (where (backend_author.name is not null) and (length(backend_author.name) > 0)),'{}') as authors_list,
coalesce(array_agg(distinct backend_tag.label) filter (where (backend_tag.label is not null) and (length(backend_tag.label) > 0)), '{}') as tag_label_list ,
coalesce(array_agg(distinct backend_tag.wiki_description) filter (where (backend_tag.wiki_description is not null) and (length(backend_tag.wiki_description) > 0)), '{}') as tag_wiki_list,
coalesce(array_agg(distinct backend_tag.custom_description) filter (where (backend_tag.custom_description is not null) and (length(backend_tag.custom_description ) > 0)), '{}') as tag_description_list
FROM backend_article
LEFT OUTER JOIN backend_article_authors ON (backend_article.id = backend_article_authors.article_id)
LEFT OUTER JOIN backend_author ON (backend_article_authors.author_id = backend_author.id)
LEFT OUTER JOIN backend_article_tags ON (backend_article.id = backend_article_tags.article_id)
LEFT OUTER JOIN backend_tag ON (backend_article_tags.tag_id = backend_tag.id)
GROUP BY backend_article.id
ORDER BY backend_article.id ASC) as lookup
where backend_article.title = lookup.title
end;
$$ LANGUAGE plpgsql;
I got error "ERROR: unexpected end of function definition at end of input LINE 27: $$ LANGUAGE plpgsql;". I am new in postgresql functions and triggers. I am open to any suggestion and solution.
CodePudding user response:
You are missing a semicolon after this:
where backend_article.title = lookup.title
Add it and this should work.