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 по дате отправления:

Предыдущее
От: Terry Lee Tucker
Дата:
Сообщение: Re: OIDs
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Foreign Key Problem