CREATE LANGUAGE plpgsql;
ALTER TABLE product_product ADD COLUMN fulltext tsvector;
CREATE INDEX product_product_fulltext_idx ON product_product USING gin(fulltext);

CREATE OR REPLACE FUNCTION product_product_fulltext_update() RETURNS trigger AS $$
    DECLARE
        txt TEXT;
        trans RECORD;
    BEGIN
        txt := NEW.code;

        FOR trans IN SELECT language, title, description FROM product_producttrans WHERE product_id = NEW.id LOOP
            txt :=  txt || ' ' ||
                    coalesce(trans.language, '') || ' ' ||
                    coalesce(trans.title, '') || ' ' ||
                    coalesce(trans.description, '');
        END LOOP;

        NEW.fulltext := to_tsvector('pg_catalog.english', txt);

        RETURN NEW;
    END
$$ LANGUAGE plpgsql;

CREATE TRIGGER product_product_fulltext_update BEFORE INSERT OR UPDATE
    ON product_product FOR EACH ROW EXECUTE PROCEDURE product_product_fulltext_update();

CREATE OR REPLACE FUNCTION product_producttrans_fulltext_update() RETURNS trigger AS $$
    DECLARE
        trans RECORD;
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            trans := OLD;
        ELSE
            trans := NEW;
        END IF;

        UPDATE product_product SET id = id WHERE id = trans.product_id;
    RETURN trans;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER product_producttrans_fulltext_update AFTER INSERT OR UPDATE OR DELETE
    ON product_producttrans FOR EACH ROW EXECUTE PROCEDURE product_producttrans_fulltext_update();