PostgreSQL 数组忽略大小写匹配 原作者:digoal/德哥 创作时间:2016-11-19 22:22:48+08 |
doudou586 发布于2016-11-22 22:22:48 评论: 1 浏览: 13952 顶: 4861 踩: 4899 |
一位兄弟的开发需求,要求不区分大小写,匹配数组内的字符串。
如下,这样的匹配。
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,将数组内的元素转换为小写。
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,希望可以帮助到你。