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