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/