Postgresql – updating associated records’ tsvectors when the primary record is updated

postgresqltrigger

I have the following trigger function that updates the object's tsvector column when its details change:

CREATE OR REPLACE FUNCTION customer_search_trigger() RETURNS trigger AS $$
    BEGIN
        NEW.customers_search_vector :=
            setweight(to_tsvector('english', CONCAT(NEW.first_name, ' ', NEW.last_name)), 'A') ||
            setweight(to_tsvector('english', NEW.email), 'A') ||
            setweight(to_tsvector('english', coalesce(NEW.physical_address,'')), 'D');
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql

Customer records are associated with Project records, via the customer_id column on the projects table. The projects table also has a tsvector column, named projects_search_vector, and that column contains tsvectors for not just the project details, but also the project's customer:

CREATE OR REPLACE FUNCTION project_search_trigger() RETURNS trigger AS $$
    DECLARE
        customer record;

    BEGIN
        SELECT first_name, last_name, email, physical_address INTO customer FROM customers_customers WHERE id = NEW.customer_id;
    NEW.projects_search_vector :=
        setweight(to_tsvector('english', coalesce(NEW.name,'')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.description,'')), 'B') ||
        setweight(to_tsvector('english', CONCAT(customer.first_name, ' ', customer.last_name)), 'D') ||
        setweight(to_tsvector('english', customer.email), 'D') ||
        setweight(to_tsvector('english', coalesce(customer.physical_address,'')), 'D');
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql

This way, when the user searches for "Bob Green", any projects that belong to Bob Green (the customer) are also returned.

QUESTION: I now want to make it so that when a customer's details change, the associated projects' tsvector columns are also updated. In other words, if the customer's last name changes from "Green" to "Brown", then when the user searches for "Brown" I want their projects to still get returned in the results.

How can I do this?

Best Answer

Make an after-update trigger on "customer" which does a dummy update on "project", which will then fire the trigger on project to do its thing:

CREATE OR REPLACE FUNCTION customer_depend_trigger() RETURNS trigger AS $$
    BEGIN
        update project set customer_id=NEW.id where customer_id=NEW.id;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

create trigger foobar after update ON customer 
    for each row execute procedure customer_depend_trigger() ;
Related Question