psql not warning while creating a wrong function?
От | Ennio-Sr |
---|---|
Тема | psql not warning while creating a wrong function? |
Дата | |
Msg-id | 20050502222515.GA18697@deby.ei.hnet обсуждение исходный текст |
Список | pgsql-novice |
[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) ]
В списке pgsql-novice по дате отправления: