[Running PG 7.4.7-5 on GNU-Linux Debian Sarge]
Hi all,
while testing whether a function written in an unusual way would
work I was fronted with what seems a strange behaviour to me, as the
function was 'regularly' created by psql, but doesn't seem to give a
correct result. AAMOF, running the same function (written in a canonical
way) gives a different result. I thought it worth informing the ML ...
These is the relevant information:
# function "unacc" (irregular layout: just to find a way to remember
# the accented chars being dealt with)
CREATE OR REPLACE FUNCTION unacc(text) RETURNS text AS '
BEGIN
RETURN translate($1,
''\311 -- É
\342 -- â
\344 -- ä
\345
\347
\350
\351
\352
\353
\354
\355
\356
\357
\360
\361
\362
\363
\364
\365
\366
\371
\372
\373
\374
\375
\377'',''eaaaceeeeiiiidnooooouuuuyy'');
END;
' LANGUAGE plpgsql IMMUTABLE STRICT ;
=> \i crea_unacc.sql # psql creates the function
CREATE FUNCTION # no warning issued
# This is my 'parole' table
id | parola
----+--------------------------------
4 | humanité
5 | café
6 | AAA
7 | BBB
8 | CCC
10 | âãäåæç89èéêëìíîï89ð
10 | ñòóôõö÷89øùúûüýþÿ8980
11 | âäåçèéêë
11 | ìíîïðñòóôõöùúûüþÿ
(.. rows)
# Result of: select * from parole where unacc(lower(parola)) like '%u%';
^^^^^
id | parola
----+-------------------------
4 | humanité
10 | âãäåæç89èéêëìíîï89ð
11 | âäåçèéêë
(3 rows)
# Result of: select * from parole where unacc(lower(parola)) like '%a%';
^^^^^
id | parola
----+----------
4 | humanité
5 | café
6 | AAA
(3 rows)
Whereas, using the canonical layout of function:
CREATE OR REPLACE FUNCTION unaccent(text) RETURNS text AS '
BEGIN
RETURN translate($1,
''\311\342\344\345\347\350\351\352\353\354\355\356\357\360\361\362\363\364\365\366\371\372\373\374\375\377'',''eaaaceeeeiiiidnooooouuuuyy'');
END;
' LANGUAGE plpgsql IMMUTABLE STRICT ;
# Result of: select * from parole where unaccent(lower(parola)) like '%u%';
^^^^^^^^
id | parola
----+----------------------------
4 | humanité
10 | ñòóôõö÷89øùúûüýþÿ8980
11 | ù
11 | ìíîïðñòóôõöùúûüþÿ
11 | ìíîïðñòóôõöùúûüýÿ
(5 rows)
# Result of: select * from parole where unaccent(lower(parola)) like '%a%';
^^^^^^^^
id | parola
----+-------------------------
4 | humanité
5 | café
6 | AAA
10 | âãäåæç89èéêëìíîï89ð
11 | âäåçèéêë
(5 rows)
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) ]