• QQ群1:5276420
  • QQ群2:3336901
  • QQ群3:254622631
  • 文档群:150657323
  • 文档翻译平台:按此
  • 邮件列表:按此订阅
微信圈:PostgresChina
http://weibo.com/postgresqlchina

搜索:
用户:

密码:

    注册  晋级


PostgreSQL携手阿里云

PostgreSQL 数组忽略大小写匹配

原作者:digoal/德哥  创作时间:2016-11-19 22:22:48+08  
doudou586 发布于2016-11-22 22:22:48           评论: 0   浏览: 10831   顶: 2466  踩: 2504 

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,希望可以帮助到你。

/images/news/2016/pg_bot_banner.jpg


评论:0   浏览: 10831                   顶: 2466  踩: 2504 

请在登录后发表评论,否则无法保存。


发表评论:

© 2010 PostgreSQL中文社区

Debug: Elapsed Time: 0.0177s / Memory Used: 2MB