useful function & operator & custom operator for Row and Array Comparisons

Postgres2015全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。本次大会嘉宾阵容强大，国内顶级PostgreSQL数据库专家将悉数到场，并特邀欧洲、俄罗斯、日本、美国等国家和地区的数据库方面专家助阵:

• Postgres-XC项目的发起人铃木市一(SUZUKI Koichi)
• Postgres-XL的项目发起人Mason Sharp
• pgpool的作者石井达夫(Tatsuo Ishii)
• PG-Strom的作者海外浩平(Kaigai Kohei)
• Greenplum研发总监姚延栋
• 周正中(德哥), PostgreSQL中国用户会创始人之一
• 汪洋，平安科技数据库技术部经理
• ……

2015年度PG大象会报名地址：http://postgres2015.eventdove.com/ PostgreSQL中国社区： http://postgres.cn/ PostgreSQL专业1群： 3336901（已满） PostgreSQL专业2群： 100910388 PostgreSQL专业3群： 150657323

社区里一位同学的需求：

请问   有什么办法可以判断 ，一个数组里面至少一个元素在一个范围之间？

  
  

   
   select 1 <= any(ARRAY[0.8,3.2]) and 3 >= any(ARRAY[0.8,3.2])
  
  
  
  

例如，这个，我喜欢的结果是f

我们注意一下，这个SQL实际上返回的是TRUE，因为分开来看 1 <= any(ARRAY[0.8,3.2])返回的是true,  3 >= any(ARRAY[0.8,3.2])返回的也是ture, 所以这个SQL返回的意思TRUE。

  
  

   
   postgres=# select 1 <= any(ARRAY[0.8,3.2]) and 3 >= any(ARRAY[0.8,3.2]);
  
  
  
  

   
    ?column? 
  
  
  
  

   
   ----------
  
  
  
  

   
    t
  
  
  
  

   
   (1 row)
  
  

所以这个SQL不能满足用户的需求，而需要使用这个数组中的每个对象和1,3对比，然后再使用bool_or聚合结果。

例如：

  
  

   
   

    
    postgres=# select 1 <= i and 3>=i from unnest(ARRAY[0.8,3.2]) t(i);
   
   
   
   

    
     ?column? 
   
   
   
   

    
    ----------
   
   
   
   

    
     f
   
   
   
   

    
     f
   
   
   
   

    
    (2 rows)
   
   
  
  
  
  

   
   

    
    postgres=# select bool_or(1 <= i and 3>=i) from unnest(ARRAY[0.8,3.2]) t(i);
   
   
   
   

    
     bool_or 
   
   
   
   

    
    ---------
   
   
   
   

    
     f
   
   
   
   

    
    (1 row)
   
   
  
  

当然还有更好的方法，那就是用范围类型和ANY构造。

  
  

   
   postgres=# select '[1,3]'::numrange @> any(array[0.8,3.1]);
  
  
  
  

   
    ?column? 
  
  
  
  

   
   ----------
  
  
  
  

   
    f
  
  
  
  

   
   (1 row)
  
  

如果你的系统不支持范围类型，则可以自定义一个函数来实现它，输入为数组以及两个数字，返回值为布尔逻辑值。

  
  

   
   postgres=# create or replace function f_cmp(a _numeric,b numeric,c numeric) returns boolean as $$
  
  
  
  

   
   declare 
  
  
  
  

   
     res boolean := false; 
  
  
  
  

   
     i numeric;
  
  
  
  

   
   begin
  
  
  
  

   
     FOREACH i in ARRAY a LOOP
  
  
  
  

   
       res := (i between b and c) or (i between c and b) or res; 
  
  
  
  

   
     END LOOP;
  
  
  
  

   
     return res;
  
  
  
  

   
   end;
  
  
  
  

   
   $$ language plpgsql strict;
  
  
  
  

   
   CREATE FUNCTION
  
  
  
  

   
   

  
  
  
  

   
   postgres=# select f_cmp(array[0.8,3.1],1,3);
  
  
  
  

   
    f_cmp 
  
  
  
  

   
   -------
  
  
  
  

   
    f
  
  
  
  

   
   (1 row)
  
  

我之前写过一个类似的例子, 模糊匹配数组中的元素。

http://blog.163.com/digoal@126/blog/static/1638770402014311115528330/

[参考]

1.  http://blog.163.com/digoal@126/blog/static/1638770402014311115528330/

2.  http://www.postgresql.org/docs/9.4/static/functions-range.html

3.  http://www.postgresql.org/docs/9.4/static/functions-comparisons.html