Re: how to ignore accents?

* Ennio-Sr <nasr.laili@tin.it> [010405, 01:40]:
> * Michael Fuhr <mike@fuhr.org> [310305, 12:24]:
> > On Thu, Mar 31, 2005 at 08:38:02PM +0200, Ennio-Sr wrote:

> OK, I tried the new version and the function was created. However,
> something strange is still happening, maybe depending on the LC_ALL
> setting. Although I created a new database with sql_ascii encoding and
> changed my LC_ALL to 'C', after inserting an accented vowel, either
> directly typing it when I was on LC_ALL=it.IT or with \code being on
> LC_ALL=C, a select * from table will show all accented vowels whereas
> the:
> non_acc=> select * from parole where unaccent(parola) = 'cafe';
>
> yealds:
>
> ERROR: column "a^çéèe^o^u^" does not exist
> CONTEXT: PL/pgSQL function "unaccent" line 2 at return
>
> [the '^' is on the vowels but I cannot write these letters with ALT+code]
>
> I will read the full documentation before any new attempts ...
>

A quick look at the docs didn't gave me any clue as to the way to tell
the function it must look at the column 'parola' for string
substitution.
See what happens:

    Table "public.parole"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
 parola | text    | not null


 id |  parola
----+----------
  4 | humanité
  5 | café
(2 rows)


 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]

# And this is what I get when I give the command:

non_acc=> select * from parole where unaccent(parola)='cafe';

ERROR: column "parola" does not exist   ----------> ???????????????
CONTEXT: PL/pgSQL function "unaccent" line 2 at return

------------------------
Am I missing something important or that type of function is not
supposed to work in 7.4.7-2?

Regards,
    Ennio.

--
[Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo.   \\?//
 Fa' qualche cosa di cui non sei capace!"  (diceva Henry Miller) ]    (°|°)
[Why use Win$ozz (I say) if ... "even a fool can do that.              )=(
 Do something you aren't good at!" (as Henry Miller used to say) ]