Re: how to ignore accents?

On Fri, Apr 01, 2005 at 08:36:35PM +0200, Ennio-Sr wrote:
>
>  CREATE FUNCTION unaccent(text) RETURNS text AS '
>   BEGIN
>       RETURN translate(&parola, "\342\347\350\351\352\364\373", "aceeeou");
>                        ^^^^^^^
>   END;
>   ' LANGUAGE plpgsql IMMUTABLE STRICT;
>
> ^^^^ [I also tried with field, &field, parola, (parola), [parameter
>       "&field?"], $1, arg1 and similar]

See the PL/pgSQL documentation to get a better idea of the syntax:

http://www.postgresql.org/docs/7.4/interactive/plpgsql.html

Let's get the basic functionality working before messing with
accented characters.  Try this:

CREATE FUNCTION unaccent(text) RETURNS text AS '
BEGIN
    RETURN translate($1, ''ABC'', ''abc'');
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;

CREATE TABLE parole (
    id      serial PRIMARY KEY,
    parola  text NOT NULL
);

INSERT INTO parole (parola) VALUES ('AAA');
INSERT INTO parole (parola) VALUES ('BBB');
INSERT INTO parole (parola) VALUES ('CCC');

SELECT unaccent(parola) FROM parole;
 unaccent
----------
 aaa
 bbb
 ccc
(3 rows)

When you get that working, then you can modify the translate()
strings to convert accented characters to their unaccented
equivalents.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/