Create a postgres function and call it with trigger

Question

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.

Yevhenii Kosmak · Accepted Answer · 2021-12-25 14:11:39Z

1

You are missing a semicolon after this:

where backend_article.title = lookup.title

Add it and this should work.

answered Dec 25, 2021 at 14:11

Yevhenii Kosmak

3,8802 gold badges13 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create a postgres function and call it with trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related