PostgreSQL 数组忽略大小写匹配

作者: digoal

日期: 2016-11-19

标签: PostgreSQL , 开发 , 数组 , any匹配 , 忽略大小写

背景

一位兄弟的开发需求，要求不区分大小写，匹配数组内的字符串。

如下，这样的匹配。postgres=# select 'a' = any(array['A','1']);

?column?

----------

f

(1 row)

需要将数组内的字符串转换为小写后匹配。postgres=# select 'a' = lower( any(array['A','1']) );

ERROR: syntax error at or near "any"

LINE 1: select 'a' = lower(any(array['A','1']));

^

postgres=# select 'a' = any( lower(array['A','1']) );

ERROR: function lower(text[]) does not exist

LINE 1: select 'a' = any(lower(array['A','1']));

^

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

在PostgreSQL中这个需求还是很好实现的，例如加操作符，或者UDF都可以。

通过操作符实现忽略大小写的数组元素匹配

添加一个基础函数，返回忽略大小写的比较结果。postgres=# create function test_ci_compare(text,text) returns boolean as $$

postgres$# select lower($1)=lower($2);

postgres$# $$ language sql strict immutable;

CREATE FUNCTION

postgres=# select test_ci_compare('a','A');

test_ci_compare

-----------------

t

(1 row)

使用这个函数创建操作符，这个操作符就是忽略大小写的了。postgres=# create operator ~= (procedure = test_ci_compare(text,text) , LEFTARG='text', rightarg='text');

CREATE OPERATOR

原来的SQL改写成如下，使用新的操作符即可postgres=# select 'a' ~= any(array['A','1']);

?column?

----------

t

(1 row)

这种方法可能不适用于索引扫描，除非你连索引OPAM也一起添加好。

UDF，对数组进行转换的方式

新增一个UDF，将数组内的元素转换为小写。postgres=# create or replace function lower(text[]) returns text[] as $$

select array_agg(lower(x)) from unnest($1) t(x);

$$ language sql strict immutable;

CREATE FUNCTION

postgres=# select lower(array['A','a']);

lower

-------

{a,a}

(1 row)

这样，就可以愉快的使用忽略大小写的匹配了，还可以继续使用索引。

postgres=# select 'a' = any( lower(array['A','1']) );

?column?

----------

t

(1 row)

一个简单的CASE，希望可以帮助到你。