Re: Text search language field

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    text NOT 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.
---------------------------------------------------------------