Skip site navigation (1) Skip section navigation (2)

Re: Text search language field

From: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
To: PGSQL-Novice <pgsql-novice(at)postgresql(dot)org>
Cc: Daniel Staal <DStaal(at)usa(dot)net>
Subject: Re: Text search language field
Date: 2012-05-13 02:15:36
Message-ID: 4FAF1948.5090709@archidevsys.co.nz (view raw or flat)
Thread:
Lists: pgsql-novice
Hi Daniel,

I can't comment on how to implement the functionality you want, but I 
have a few comments you may (or may not!) find useful.

 1. Field names need not be in double quotes.
 2. if a character field is Always 3 characters, then say so
 3. add NOT NULL where appropriate (you may well decide more fields
    aught to be NOT NULL)
 4. keeping primary and foreign key fields separate from user visible data.
 5. identifying primary and foreign key fields clearly
 6. suggest table names be singular (my convention, not universally
    adaopted!)

The following SQL is syntactically correct (at least psql did not object 
to it...)

CREATE TABLE language
(
     id              SERIAL PRIMARY KEY,
     code            char(3) NOT NULL,
     description     text
);

CREATE TABLE resource
(
     id              SERIAL PRIMARY KEY,
     text_element    textNOT NULL,
     text_search     tsvector,
     language_id     int REFERENCES language(id)NOT NULL
);



On 13/05/12 12:08, Daniel Staal wrote:
>
> I'm working on project with some large text areas that will need to be 
> searched, and I'm trying to set up an indexed text search field to 
> make things a bit smoother.  Only one of the top requirements is that 
> this has to be multi-lingual, so I should be using the correct 
> language's text search.  The table stores the language the text is in, 
> so in theory this should be easy...  But it seems to be a bit more 
> subtle than it first appears.  Any ideas on the best way to set this up?
>
> Tables:
>
> Resource:
>    "text_element"    text,
>    "text_search"    tsvector,
>    "language"        char varying(3) references "languages"
>
> languages  (This is 'static' list of ISO 639-3 codes, to make sure 
> everything uses the same codes):
>    "code" character varying(3) primary key,
>    "description" character varying(100)
>
> Trigger (This is my first draft):
> create trigger "textsearch_trig" before update or insert
>   on "resource" for each row execute procedure
>   tsvector_update_trigger_column("text_search", "language", 
> "text_element");
>
> The trigger currently throws an error 'column "language" is not of 
> regconfig type' whenever I try to use it, and I'm looking for the best 
> way to solve that.  The 'simple' solution of 'create text search 
> configuration eng ( copy = pg_catalog.english );' didn't work.  Before 
> I start into a long exploration into why and what should be done about 
> it, I thought I'd seek wisdom.  ;)
>
> Daniel T. Staal
>
> ---------------------------------------------------------------
> This email copyright the author.  Unless otherwise noted, you
> are expressly allowed to retransmit, quote, or otherwise use
> the contents for non-commercial purposes.  This copyright will
> expire 5 years after the author's death, or in 30 years,
> whichever is longer, unless such a period is in excess of
> local copyright law.
> ---------------------------------------------------------------
>

In response to

Responses

pgsql-novice by date

Next:From: Daniel StaalDate: 2012-05-13 02:56:46
Subject: Re: Text search language field
Previous:From: Daniel StaalDate: 2012-05-13 00:08:42
Subject: Text search language field

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group