unaccent() не работает с греческими буквами в динамическом запросе plpgsql

Я использую PostgreSQL 10 и запускаю CREATE EXTENSION unaccent; успешно. У меня есть функция plgsql, которая содержит следующиеwhereText := 'lower(unaccent(place.name)) LIKE lower(unaccent($1))';

позже, в зависимости от того, что выбрал пользователь, в whereText,

whereText наконец используется в запросе:

placewithkeys := '%'||placename||'%';
RETURN QUERY EXECUTE format('SELECT id, name FROM '||fromText||' WHERE '||whereText)
     USING  placewithkeys , event, date;

whereText := 'LOWER(unaccent(place.name)) LIKE LOWER(unaccent($1))'; не работает, даже если я удаляю LOWER часть.

я делаю select __my_function('Τζι'); и я ничего не получаю обратно, хотя я должен вернуть результаты, потому что в базе данных есть имя Τζίμα

Если я удалю unaccent и оставить LOWER это работает, но не для акцентов: τζ приносит Τζίμα назад как следует. Кажется, что unaccent вызывает проблему.

Что мне не хватает? Как я могу это исправить?

Поскольку были комментарии о синтаксисе и возможном SQLi, я предоставляю полное определение функции, теперь измененное на греческий без учета ударения и без учета регистра:

CREATE  FUNCTION __a_search_place
(placename text, eventtype integer, eventdate integer, eventcentury integer, constructiondate integer, constructioncentury integer, arstyle integer, artype integer)
RETURNS TABLE
(place_id bigint, place_name text, place_geom geometry) 
AS $$
DECLARE
selectText text;
fromText text;
whereText text;
usingText text; 
placewithkeys text;
BEGIN   
    fromText := '
    place
    JOIN cep ON place.id = cep.place_id
    JOIN event ON cep.event_id = event.id                     
    ';  
    whereText := 'unaccent(place.name) iLIKE  unaccent($1)';   
    placewithkeys := '%'||placename||'%';
    IF constructiondate IS NOT NULL OR constructioncentury IS NOT NULL OR arstyle IS NOT NULL OR artype IS NOT NULL THEN
        fromText := fromText || '
        JOIN construction ON cep.construction_id = construction.id
        JOIN construction_atype ON construction.id = construction_atype.construction_id
        JOIN construction_astyle ON construction.id = construction_astyle.construction_id
        JOIN atype ON atype.id = construction_atype.atype_id
        JOIN astyle ON astyle.id = construction_astyle.astyle_id  
        ';   
    END IF;    
    IF eventtype IS NOT NULL THEN
        whereText := whereText || 'AND event.type = $2 ';
    END IF;
    IF eventdate IS NOT NULL THEN
        whereText := whereText || 'AND event.date = $3 ';
    END IF;
    IF eventcentury IS NOT NULL THEN
        whereText := whereText || 'AND event.century = $4 ';
    END IF;    
    IF constructiondate IS NOT NULL THEN
        whereText := whereText || 'AND construction.date = $5 ';
    END IF;
    IF constructioncentury IS NOT NULL THEN
        whereText := whereText || 'AND construction.century = $6 ';
    END IF;
    IF arstyle IS NOT NULL THEN
        whereText := whereText || 'AND astyle.id = $7 ';
    END IF;
    IF artype IS NOT NULL THEN
        whereText := whereText || 'AND atype.id = $8 ';
    END IF;   
    whereText := whereText || '    
    GROUP BY place.id, place.geom, place.name
    ';    

    RETURN QUERY EXECUTE format('SELECT place.id, place.name, place.geom FROM '||fromText||' WHERE '||whereText)      
    USING  placewithkeys, eventtype, eventdate, eventcentury, constructiondate, constructioncentury, arstyle, artype ;

END;
$$
LANGUAGE plpgsql;
Источник

1 ответ

Решение

Я могу подтвердить, unaccent() в настоящее время, кажется, не работает для греческих букв. Вызов:

SELECT unaccent('
ἀ ἁ ἂ ἃ ἄ ἅ ἆ ἇ Ἀ Ἁ Ἂ Ἃ Ἄ Ἅ Ἆ Ἇ
ἐ ἑ ἒ ἓ ἔ ἕ         Ἐ Ἑ Ἒ Ἓ Ἔ Ἕ     
ἠ ἡ ἢ ἣ ἤ ἥ ἦ ἧ Ἠ Ἡ Ἢ Ἣ Ἤ Ἥ Ἦ Ἧ
ἰ ἱ ἲ ἳ ἴ ἵ ἶ ἷ Ἰ Ἱ Ἲ Ἳ Ἴ Ἵ Ἶ Ἷ
ὀ ὁ ὂ ὃ ὄ ὅ         Ὀ Ὁ Ὂ Ὃ Ὄ Ὅ     
ὐ ὑ ὒ ὓ ὔ ὕ ὖ ὗ     Ὑ   Ὓ   Ὕ   Ὗ
ὠ ὡ ὢ ὣ ὤ ὥ ὦ ὧ Ὠ Ὡ Ὢ Ὣ Ὤ Ὥ Ὦ Ὧ
ὰ ά ὲ έ ὴ ή ὶ ί ὸ ό ὺ ύ ὼ ώ     
ᾀ ᾁ ᾂ ᾃ ᾄ ᾅ ᾆ ᾇ ᾈ ᾉ ᾊ ᾋ ᾌ ᾍ ᾎ ᾏ
ᾐ ᾑ ᾒ ᾓ ᾔ ᾕ ᾖ ᾗ ᾘ ᾙ ᾚ ᾛ ᾜ ᾝ ᾞ ᾟ
ᾠ ᾡ ᾢ ᾣ ᾤ ᾥ ᾦ ᾧ ᾨ ᾩ ᾪ ᾫ ᾬ ᾭ ᾮ ᾯ
ᾰ ᾱ ᾲ ᾳ ᾴ   ᾶ ᾷ Ᾰ Ᾱ Ὰ Ά ᾼ ᾽ ι ᾿
῀ ῁ ῂ ῃ ῄ   ῆ ῇ Ὲ Έ Ὴ Ή ῌ ῍ ῎ ῏
ῐ ῑ ῒ ΐ         ῖ ῗ Ῐ Ῑ Ὶ Ί     ῝ ῞ ῟
ῠ ῡ ῢ ΰ ῤ ῥ ῦ ῧ Ῠ Ῡ Ὺ Ύ Ῥ ῭ ΅ `
        ῲ ῳ ῴ   ῶ ῷ Ὸ Ό Ὼ Ώ ῼ ´ ῾ ');

... возвращает все буквы без изменений, без диакритических знаков, как мы и ожидали.
(Я извлек этот список со страницы Википедии о греческих диакритиках.)

Похоже на недостаток unaccent модуля. Вы можете продлить по умолчанию unaccent словарь или создать свой собственный. В руководстве есть инструкции. Я создал несколько словарей в прошлом, и это просто. И вам не нужно это в первую очередь:

Postgres unaccent правила для греческих персонажей:

Unaccent правила плюс греческие символы для Postgres 9.6:

Вам необходим доступ на запись в файловую систему сервера, однако - в каталог, содержащий неактивные файлы. Таким образом, это невозможно на большинстве облачных сервисов...

Или вы можете сообщить об ошибке и попросить включить греческие диакритические знаки.

В сторону: Dyamic SQL и SQLi

Представленные вами фрагменты кода не подвержены внедрению SQL. $1 объединяется в виде буквенной строки и разрешается только в EXECUTE команда, где значение безопасно передается с USING пункт. Так что никакой небезопасной конкатенации там нет. Я бы сделал это так:

RETURN QUERY EXECUTE format(
   $q$
   SELECT id, name
   FROM   place ... 
   WHERE  lower(unaccent(place.name)) LIKE '%' || lower(unaccent($1)) || '%'
   $q$
   )
USING  placename, event, date;

Заметки:

  • Менее запутанно - ваш оригинал даже запутал Павла в комментариях, профессионал в этой области.

  • Назначения в plpgsql немного дороже (больше, чем в других PL), поэтому используйте стиль кодирования с несколькими назначениями.

  • Объединить два % символы для LIKE непосредственно в основной запрос, предоставляя планировщику запросов информацию о том, что шаблон не привязан к началу или концу, что может помочь более эффективному плану. Только пользовательский ввод (безопасно) передается как переменная.

  • Так как ваш WHERE таблица ссылок на предложения place, FROM в любом случае, эта статья должна включать эту таблицу. Таким образом, вы не можете объединить предложение FROM независимо для начала. Наверное, лучше держать все это в одном format(),

  • Используйте котировки доллара, чтобы вам не пришлось дополнительно избегать одинарных кавычек.

  • Может быть, просто использовать ILIKE вместо lower(...) LIKE lower(...), Если вы работаете с индексами триграмм (вроде бы лучше для этого запроса): они работают с ILIKE также:

  • Я предполагаю, что вы знаете, что вам может понадобиться экранировать символы со специальным LIKE шаблон?

Проверяемая функция

После того, как вы добавили полную функцию в запрос...

CREATE OR REPLACE FUNCTION __a_search_place(
        placename             text
      , eventtype             int = NULL
      , eventdate             int = NULL
      , eventcentury          int = NULL
      , constructiondate      int = NULL
      , constructioncentury   int = NULL
      , arstyle               int = NULL
      , artype                int = NULL)
  RETURNS TABLE(place_id bigint, place_name text, place_geom geometry) AS
$func$
BEGIN
   -- RAISE NOTICE '%', concat_ws(E'\n' -- to debug
   RETURN QUERY EXECUTE concat_ws(E'\n'
 ,'SELECT p.id, p.name, p.geom
   FROM   place p
   WHERE  unaccent(p.name) ILIKE (''%'' || unaccent($1) || ''%'')'  -- no $-quotes
              -- any input besides placename ($1)
, CASE WHEN NOT ($2,$3,$4,$5,$6,$7,$8) IS NULL THEN
  'AND    EXISTS (
      SELECT
      FROM   cep
      JOIN   event e ON e.id = cep.event_id' END
               -- constructiondate, constructioncentury, arstyle, artype
 , CASE WHEN NOT ($5,$6,$7,$8) IS NULL THEN

     'JOIN   construction    con ON cep.construction_id = con.id
      JOIN   construction_atype  ON con.id = construction_atype.construction_id
      JOIN   construction_astyle ON con.id = construction_astyle.construction_id' END
              -- arstyle, artype
, CASE WHEN NOT ($7,$8) IS NULL THEN
     'JOIN   atype               ON atype.id = construction_atype.atype_id
      JOIN   astyle              ON astyle.id = construction_astyle.astyle_id' END
 , CASE WHEN NOT ($2,$3,$4,$5,$6,$7,$8) IS NULL THEN
     'WHERE  cep.place_id = p.id' END
 , CASE WHEN eventtype           IS NOT NULL THEN 'AND e.type = $2'      END
 , CASE WHEN eventdate           IS NOT NULL THEN 'AND e.date = $3'      END
 , CASE WHEN eventcentury        IS NOT NULL THEN 'AND e.century = $4'   END
 , CASE WHEN constructiondate    IS NOT NULL THEN 'AND con.date = $5'    END
 , CASE WHEN constructioncentury IS NOT NULL THEN 'AND con.century = $6' END
 , CASE WHEN arstyle             IS NOT NULL THEN 'AND astyle.id = $7'   END
 , CASE WHEN artype              IS NOT NULL THEN 'AND atype.id = $8'    END
 , CASE WHEN NOT ($2,$3,$4,$5,$6,$7,$8) IS NULL THEN
     ')' END
   );
   USING  placename
        , eventtype
        , eventdate
        , eventcentury
        , constructiondate
        , constructioncentury
        , arstyle
        , artype;
END
$func$  LANGUAGE plpgsql;

Это полное переписывание функции. Должно сделать функцию значительно быстрее из-за нескольких улучшений. Также SQLi-безопасный (как ваша оригинальная версия). Должно быть функционально идентичным вашему оригиналу - за исключением случаев, когда я присоединяю меньшее количество таблиц, которые могут не фильтровать строки, которые фильтруются путем объединения только с таблицами.

Основные характеристики:

Источник
Другие вопросы по тегам