Login

PostgreSQL fulltext with language translations

Author:
diverman
Posted:
August 15, 2010
Language:
SQL
Version:
1.2
Tags:
sql models translations model full-text postgres postgresql language fulltext translation
Score:
1 (after 1 ratings)

Consider following models:

class Product(models.Model):
    code = modeld.CharField()

class ProductTrans(models.Model):
    product = models.ForeignKey('Product')
    language = models.ChoiceField(choices=settings.LANGUAGES)
    title = models.ChaField()
    description = models.ChaField()

With this snippet is possible search through all translations of product at the same time (using string concatenation in trigger):

Product.objects.extra(
    where = ['product_product.fulltext @@ to_tsquery(%s)'],
    params = [ 'someproduct' ]
)

For PostgreSQL >=8.4 only.

 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. create_template_postgis-ubuntu_lucid by clawlor 10 years, 2 months ago
  2. PostgreSQL fulltext with language translations by diverman 10 years, 3 months ago
  3. Drop all tables in MySQL database by mpasternacki 10 years, 10 months ago
  4. grep and delete sqlite tables by kifkif 10 years, 11 months ago
  5. Substitute hyphens with spaces to enale URLs to reference to multi-word tags by miernik 11 years ago

Comments

Please login first before commenting.