PostgreSQL full text search tsv column trigger with many-to-many

Question

I have a tsv column on my campaign table and I also added a trigger to update it every time the row changes. Tsv is currently constructed from a few table columns. The table also has multiple tags via many-to-many relation. My question is, is it possible to write such a trigger that would also pull in all the tags on row INSERT and update the tsv column when tags are added or removed from a campaign? I already know how to construct a concat document in a query (eg: Full text search on multiple joined tables). I imagine this could work if I wrote another trigger on the many-to-many intermediate table that would update campaigns tsv or something like that.

Edit: This is what I came up with so far and it seems to work but I would like to get an opinion about the whole thing. I have triggers for campaign INSERT, campaign column name UPDATE, INSERT/UPDATE on campaign_tags and one for campaign_tags DELETE. Each one updates the campaign.tsv but in slightly different way.

ALTER TABLE campaigns ADD COLUMN tsv tsvector;

CREATE INDEX campaigns_tsv_idx ON newsletters USING GIN (tsv);

campaigns_tsv_update

CREATE OR REPLACE FUNCTION campaigns_tsv_update() RETURNS trigger AS $$
begin
  SELECT setweight(to_tsvector(unaccent(coalesce(name, ' '))), 'A') || 
           setweight(to_tsvector(coalesce((string_agg(tags.value, ' ')), '')), 'D') 
           INTO new.tsv 
           FROM campaigns 
           LEFT JOIN campaign_tags ON campaign_tags.campaign_id = campaigns.id 
           LEFT JOIN tags ON tags.id = campaign_tags.campaign_tags_id
           WHERE campaigns.id=new.id
           GROUP BY campaigns.id;
  RAISE NOTICE 'campaigns_tsv_trigger new is %', new;
  return new;
end
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS campaigns_tsv_trigger_update ON campaigns;
CREATE TRIGGER campaigns_tsv_trigger_update BEFORE UPDATE OF name
    ON campaigns FOR EACH ROW EXECUTE PROCEDURE campaigns_tsv_update();

campaigns_tsv_insert

CREATE OR REPLACE FUNCTION campaigns_tsv_insert() RETURNS trigger AS $$
begin
  new.tsv := setweight(to_tsvector(unaccent(coalesce(new.name, ' '))), 'A');
  return new;
end
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS campaigns_tsv_trigger_insert ON campaigns;
CREATE TRIGGER campaigns_tsv_trigger_insert BEFORE INSERT
    ON campaigns FOR EACH ROW EXECUTE PROCEDURE campaigns_tsv_insert();

campaigns_tags_tsv_update

CREATE OR REPLACE FUNCTION campaign_tags_tsv_update() RETURNS trigger AS $$
begin

UPDATE campaigns SET tsv=subquery.document FROM (
    SELECT setweight(to_tsvector(unaccent(coalesce(name, ' '))), 'A') || 
           setweight(to_tsvector(coalesce((string_agg(tags.value, ' ')), '')), 'D') AS document 
           FROM campaigns 
           LEFT JOIN campaign_tags ON campaign_tags.campaign_id = campaigns.id 
           LEFT JOIN tags ON tags.id = campaign_tags.campaign_tags_id
           WHERE campaigns.id=new.campaign_id
           GROUP BY campaigns.id
) AS subquery WHERE campaigns.id=new.campaign_id;
RAISE NOTICE 'campaign_tags_tsv_update new is %', new;

return new;

end;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS campaigns_tags_tsv_trigger ON campaign_tags;
CREATE TRIGGER campaigns_tags_tsv_trigger AFTER INSERT OR UPDATE
    ON campaign_tags FOR EACH ROW EXECUTE PROCEDURE campaign_tags_tsv_update();

campaigns_tags_tsv_update_del

CREATE OR REPLACE FUNCTION campaign_tags_tsv_update_del() RETURNS trigger AS $$
begin

UPDATE campaigns SET tsv=subquery.document FROM (
    SELECT setweight(to_tsvector(unaccent(coalesce(name, ' '))), 'A') || 
           setweight(to_tsvector(coalesce((string_agg(tags.value, ' ')), '')), 'D') AS document 
           FROM campaigns 
           LEFT JOIN campaign_tags ON campaign_tags.campaign_id = campaigns.id 
           LEFT JOIN tags ON tags.id = campaign_tags.campaign_tags_id
           WHERE campaigns.id=old.campaign_id
           GROUP BY campaigns.id
) AS subquery WHERE campaigns.id=old.campaign_id;
RAISE NOTICE 'campaign_tags_tsv_update_del old is %', old;

return old;

end;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS campaigns_tags_tsv_trigger_old ON campaign_tags;
CREATE TRIGGER campaigns_tags_tsv_trigger_old AFTER DELETE
    ON campaign_tags FOR EACH ROW EXECUTE PROCEDURE campaign_tags_tsv_update_del();

Answer 1

This is what I came up with so far and it seems to work but I would like to get an opinion about the whole thing.

Incoming.. opinion. Don't use triggers for this. It's wayyy to complex. You have three piece of functionality that will make life easier.

1. Function indexes.
2. Views.
3. Materialized views: write a query that generates the output table that you want to query simply issue REFRESH MATERIALIZED VIEW mymatview; to update it. How often do you have to reflect tag updates anyway? A materialized view can itself use function indexes, or any other kind of index. For instance, you can create a Materialized View of the tsvectors of multiple columns from multiple tables, and then write a function index that uses native functionality over those multiple tsvector columns. Then when you want, periodically run REFRESH MATERIALIZED VIEW mymatview.

You may also want to restructure how you're doing tags. In PG, you can easily represent them on the table itself with an array type which is easily indexed.

Update

This code just strikes me as awkward. We call this a DEFAULT. Don't implement that as a trigger.

CREATE OR REPLACE FUNCTION campaigns_tsv_insert() RETURNS trigger AS $$
begin
  new.tsv := setweight(to_tsvector(unaccent(coalesce(new.name, ' '))), 'A');
  return new;
end
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS campaigns_tsv_trigger_insert ON campaigns;
CREATE TRIGGER campaigns_tsv_trigger_insert BEFORE INSERT
    ON campaigns FOR EACH ROW EXECUTE PROCEDURE campaigns_tsv_insert();

Instead

ALTER TABLE campaigns ADD COLUMN tsv tsvector DEFAULT setweight(to_tsvector(unaccent(coalesce(name, ' '))), 'A');