Re: Full text index without accents

This would probably help:=20

CREATE OR REPLACE FUNCTION norm_text_latin(character varying)
  RETURNS character varying AS
$BODY$
declare
	p_str		alias for $1;
	v_str		varchar;
begin
	select translate(p_str, '=C3=80=C3=81=C3=82=C3=83=C3=84=C3=85', 'AAAAAA')
=
into v_str;
	select translate(v_str, '=C3=89=C3=88=C3=8B=C3=8A', 'EEEE') into v_str;
	select translate(v_str, '=C3=8C=C3=8D=C3=8E=C3=8F', 'IIII') into v_str;
	select translate(v_str, '=C3=8C=C3=8D=C3=8E=C3=8F', 'IIII') into v_str;
	select translate(v_str, '=C3=92=C3=93=C3=94=C3=95=C3=96', 'OOOOO') into
v_=
str;
	select translate(v_str, '=C3=99=C3=9A=C3=9B=C3=9C', 'UUUU') into v_str;
	select translate(v_str, '=C3=A0=C3=A1=C3=A2=C3=A3=C3=A4=C3=A5', 'aaaaaa')
=
into v_str;
	select translate(v_str, '=C3=A8=C3=A9=C3=AA=C3=AB', 'eeee') into v_str;
	select translate(v_str, '=C3=AC=C3=AD=C3=AE=C3=AF', 'iiii') into v_str;
	select translate(v_str, '=C3=B2=C3=B3=C3=B4=C3=B5=C3=B6', 'ooooo') into
v_=
str;
	select translate(v_str, '=C3=B9=C3=BA=C3=BB=C3=BC', 'uuuu') into v_str;
	select translate(v_str, '=C3=87=C3=A7', 'Cc') into v_str;
	return v_str;
end;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

There's also o useful functions here:
http://www.project-open.org/doc/intranet-search-pg/intranet-search-pg-creat=
e.sql




-----Original Message-----
From: pgsql-general-owner@[EMAIL PROTECTED]
 On Behalf Of Fco. Mario Barcala Rodr=C3=ADguez
Sent: July 24, 2008 4:47 AM
To: pgsql-general@[EMAIL PROTECTED]
 Re: [GENERAL] 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=3Dreplace($1, '=C3=A1', 'a');
  var1=3Dreplace(var1, '=C3=A9', 'e');
  var1=3Dreplace(var1, '=C3=AD', 'i');
  var1=3Dreplace(var1, '=C3=B3', 'o');
  var1=3Dreplace(var1, '=C3=BA', 'u');
  var1=3Dreplace(var1, '=C3=81', 'A');
  var1=3Dreplace(var1, '=C3=89', 'E');
  var1=3Dreplace(var1, '=C3=8D', 'I');
  var1=3Dreplace(var1, '=C3=93', 'O');
  var1=3Dreplace(var1, '=C3=9A', '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=20


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



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


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