作者
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，希望可以帮助到你。
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
