Login

PostgreSQL fulltext with language translations

Author:
diverman
Posted:
August 15, 2010
Language:
SQL
Version:
1.2
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 14 years, 2 months ago
  2. Drop all tables in MySQL database by mpasternacki 14 years, 9 months ago
  3. grep and delete sqlite tables by kifkif 14 years, 11 months ago
  4. Substitute hyphens with spaces to enale URLs to reference to multi-word tags by miernik 15 years ago
  5. oneliner to delete SQLite tables by kifkif 15 years ago

Comments

Please login first before commenting.