Re: Text search language field
От | Gavin Flower |
---|---|
Тема | Re: Text search language field |
Дата | |
Msg-id | 4FAF1948.5090709@archidevsys.co.nz обсуждение исходный текст |
Ответ на | Text search language field (Daniel Staal <DStaal@usa.net>) |
Список | 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.
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 can't comment on how to implement the functionality you want, but I have a few comments you may (or may not!) find useful.
- Field names need not be in double quotes.
- if a character field is Always 3 characters, then say so
- add NOT NULL where appropriate (you may well decide more fields aught to be NOT NULL)
- keeping primary and foreign key fields separate from user visible data.
- identifying primary and foreign key fields clearly
- suggest table names be singular (my convention, not universally adaopted!)
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.
---------------------------------------------------------------
В списке pgsql-novice по дате отправления: