Re: Full text index without accents

Finally I create a function like:

CREATE OR REPLACE FUNCTION nonsensible (text) RETURNS text AS $$
DECLARE
  var1 varchar;
BEGIN
  var1=replace($1, 'á', 'a');
  var1=replace(var1, 'é', 'e');
  var1=replace(var1, 'í', 'i');
  var1=replace(var1, 'ó', 'o');
  var1=replace(var1, 'ú', 'u');
  var1=replace(var1, 'Á', 'A');
  var1=replace(var1, 'É', 'E');
  var1=replace(var1, 'Í', 'I');
  var1=replace(var1, 'Ó', 'O');
  var1=replace(var1, 'Ú', 'U');
  return var1;
END
$$LANGUAGE plpgsql immutable;

Then, create text indexes, one for sensible queries and other for
unsensible ones:

CREATE INDEX textindex ON do***ent USING
gin(to_tsvector('spanish',text));

CREATE INDEX textindexn ON do***ent USING
gin(to_tsvector('spanish',nonsensible(text)));

And then make a query sensible or unsensible to accents doing:

SELECT id FROM do***ent WHERE to_tsvector('spanish',text) @[EMAIL PROTECTED]
('spanish','word_with_accent');

or:

SELECT id FROM do***ent WHERE to_tsvector('spanish',nonsensible(text))
@[EMAIL PROTECTED]
 to_tsquery('spanish',nonsensible('word_with_accent'));
respectively.

I think postgreSQL uses both indexes as necessary. I believe to remember
reading something about it in the do***entation.

Thank you very much,

  Mario Barcala 


> Here is an example
> 
> CREATE FUNCTION dropatsymbol(text) RETURNS text
> AS 'select replace($1, ''@[EMAIL PROTECTED]
'', '' '');'
> LANGUAGE SQL;
> 
> arxiv=# select to_tsvector('english',dropatsymbol('oleg@[EMAIL PROTECTED]
'));
>         to_tsvector
> -------------------------
>   'oleg':1 'sai.msu.su':2



-- 
Sent via pgsql-general mailing list (pgsql-general@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general