postgresql - unaccent() 不适用于 plpgsql 动态查询中的希腊字母

Question

我使用 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;

Answer 1

Postgres 12
unaccent()现在也适用于希腊字母。删除变音符号:

分贝<> fiddle here

Quoting the release notes:

Allow unaccent to remove accents from Greek characters (Tasos Maschalidis)

Postgres 11 或以上
unaccent()还不适用于希腊字母。电话:

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

... 返回所有字母不变，没有像我们期望的那样删除变音符号。
(我从 Wikipedia page on Greek diacritics 中提取了这个列表。)

分贝<> fiddle here

貌似是 unaccent module的一个缺点.您可以扩展默认 unaccent字典或创建自己的。说明书上有说明。我过去创建了几个字典，这很简单。你不是首先需要这个:

Postgres 希腊字符的重音规则:

https://gist.github.com/jfragoulis/9914900

Postgres 9.6 的非重音规则加上希腊字符:

https://gist.github.com/marinoszak/7d5d6a8670faae0f4589c2da988f2ba3

但是，您需要对服务器的文件系统进行写访问 - 包含非重音文件的目录。所以，在大多数云服务上是不可能的......

或者你可以 report a bug并要求包括希腊变音符号。

旁白:动态 SQL 和 SQLi

您提供的代码片段是 不是 容易受到 SQL 注入(inject)攻击。 $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;

笔记:

不那么令人困惑 - 您原来在评论中甚至混淆了 Pavel，该领域的专业人士。

plpgsql 中的赋值稍贵(比其他 PL 更昂贵)，因此采用很少赋值的编码风格。

连接两者 % LIKE 的符号直接进入主查询，为查询规划器提供模式未 anchor 定到开始或结束的信息，这可能有助于更有效的计划。只有用户输入(安全)作为变量传递。

由于您的 WHERE条款引用表place , FROM无论如何，子句都需要包含此表。所以你不能独立地连接 FROM 子句开始。可能更好地将其全部保存在一个 format() 中.

使用美元引用，这样您就不必另外转义单引号。

Insert text with single quotes in PostgreSQL

What are '$$' used for in PL/pgSQL

也许只需使用 ILIKE而不是 lower(...) LIKE lower(...) .如果您使用 trigram 索引(对于此查询来说似乎最好):那些与 ILIKE 一起使用还有:

LOWER LIKE vs iLIKE

我假设您知道您可能需要转义 LIKE 中具有特殊含义的字符。图案？

How to escape string while matching pattern in PostgreSQL

Escape function for regular expression or LIKE patterns

审计功能

在您提供完整的功能后...

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 安全的(就像你的原版一样)。应该在功能上相同 除了 我加入较少表的情况，这可能不会过滤通过单独加入表过滤的行。

主要特点:

使用 EXISTS()而不是外层的大量连接加上 GROUP BY .这为更好的性能贡献了最大的份额。有关的:

Search a JSON array for an object containing a value matching a pattern

format()通常是从用户输入连接 SQL 的不错选择。但是由于您封装了所有代码元素并且只传递标志，因此在这种情况下您不需要它。相反，concat_ws()是有帮助的。有关的:

How to concatenate columns in a Postgres SELECT?

仅连接您实际需要的 JOIN。

更少的任务，更短的代码。

参数的默认值。允许缺少参数的简化调用。喜欢:

SELECT __a_search_place('foo', 2, 3, 4);
SELECT __a_search_place('foo');

有关的:

Optional argument in PL/pgSQL function

关于短ROW()用于测试任何值是否为 NOT NULL 的语法:

Why is IS NOT NULL false when checking a row type?