PostgreSQL fulltext with language translations

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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();

More like this

  1. i18n base model for translatable content by foxbunny 5 years, 9 months ago
  2. Multilingual Models by Archatas 7 years, 1 month ago
  3. Function/Stored Procedure Manager by axiak 6 years, 10 months ago
  4. Changing field type in production by lawgon 7 years ago
  5. auto image field w/ prepopulate_from & default by zbyte64 5 years, 8 months ago

Comments

(Forgotten your password?)