PostgreSQL customize inet_aton, inet_ntoa function in text type like MySQL.

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

PostgreSQL的类型支持比较强大, 除了主流的文本, 日期, 时间, 数字, 还支持JSON, 货币, 网络, 几何, 枚举, 数组, 范围, XML, 布尔, 全文检索, 比特, 复合等数据类型. 

MySQL的类型支持比较薄弱, MySQL里面没有专门的网络数据类型, 如果要存储网络信息, 一般的做法是转换成INT类型来处理.

PostgreSQL当然没有必要这么做, 因为已经支持inet和cidr类型了, 操作极为方便. 开发人员使用PostgreSQL会比较愉快.

例如

>>

contains

inet '192.168.1/24' >> inet '192.168.1.5'

  
  

   
   digoal=# select inet '192.168.1/24' >> inet '192.168.2.1'
  
  
  
  

   
   digoal-# ;
  
  
  
  

   
    ?column? 
  
  
  
  

   
   ----------
  
  
  
  

   
    f
  
  
  
  

   
   (1 row)
  
  
  
  

   
   

  
  
  
  

   
   digoal=# select inet '192.168.1/24' >> inet '192.168.2.1';
  
  
  
  

   
    ?column? 
  
  
  
  

   
   ----------
  
  
  
  

   
    f
  
  
  
  

   
   (1 row)
  
  
  
  

   
   

  
  
  
  

   
   digoal=# select inet '192.168.1/24' >> inet '192.168.1.100';
  
  
  
  

   
    ?column? 
  
  
  
  

   
   ----------
  
  
  
  

   
    t
  
  
  
  

   
   (1 row)
  
  
  
  

   
   

  
  
  
  

   
   digoal=# select inet '192.168.1.0/24' >> inet '192.168.1.100';
  
  
  
  

   
    ?column? 
  
  
  
  

   
   ----------
  
  
  
  

   
    t
  
  
  
  

   
   (1 row)
  
  
  
  

   
   

  
  
  
  

   
   digoal=# select inet '192.168.1.128/25' >> inet '192.168.1.100';
  
  
  
  

   
    ?column? 
  
  
  
  

   
   ----------
  
  
  
  

   
    f
  
  
  
  

   
   (1 row)
  
  
  
  

   
   

  
  
  
  

   
   digoal=# select inet '192.168.1.128/25' >> inet '192.168.1.129';
  
  
  
  

   
    ?column? 
  
  
  
  

   
   ----------
  
  
  
  

   
    t
  
  
  
  

   
   (1 row)
  
  

本文模拟在没有INET或者cidr类型的时候的使用方法.

那么我这里要说的是如果你是从mysql转到pg的, 并且前期不想改代码的话, 那么就需要写一些和MySQL兼容的函数.

例如MySQL里面的inet_aton , inet_ntoa.

•  INET_ATON(expr)

  Given the dotted-quad representation of an IPv4 network address as a string, returns an integer that represents the numeric value of the address in network byte order (big endian). INET_ATON() returns NULL if it does not understand its argument.

  mysql> SELECT INET_ATON('10.0.5.9');
          -> 167773449
  

  For this example, the return value is calculated as 10×256³ + 0×256² + 5×256 + 9.

  INET_ATON() may or may not return a non-NULL result for short-form IP addresses (such as '127.1' as a representation of '127.0.0.1'). Because of this, INET_ATON()a should not be used for such addresses.

  Note

  To store values generated by INET_ATON(), use an INT UNSIGNED column rather than INT, which is signed. If you use a signed column, values corresponding to IP addresses for which the first octet is greater than 127 cannot be stored correctly. See Section 11.2.6, “Out-of-Range and Overflow Handling”.

•  INET_NTOA(expr)

  Given a numeric IPv4 network address in network byte order, returns the dotted-quad string representation of the address as a nonbinary string in the connection character set. INET_NTOA() returns NULL if it does not understand its argument.

  mysql> SELECT INET_NTOA(167773449);
          -> '10.0.5.9'

在PostgreSQL可以通过如下自定义函数来实现 : 

  
  

   
   create or replace function inet_aton(ip text) returns int8 as $$
  
  
  
  

   
   declare
  
  
  
  

   
     v int;
  
  
  
  

   
     res int8 := 0;
  
  
  
  

   
     i int := 3;
  
  
  
  

   
   begin
  
  
  
  

   
     foreach v in array string_to_array(ip, '.') loop
  
  
  
  

   
       res := res+v*(256^i);
  
  
  
  

   
       i := i-1;
  
  
  
  

   
     end loop;
  
  
  
  

   
     return res;
  
  
  
  

   
   end;
  
  
  
  

   
   $$ language plpgsql;
  
  
  
  

   
   

  
  
  
  

   
   create or replace function inet_ntoa(ip int8) returns text as $$
  
  
  
  

   
   declare
  
  
  
  

   
     res text := '';
  
  
  
  

   
   begin
  
  
  
  

   
     res := res || ((ip >> 24) & (2^8-1)::int);
  
  
  
  

   
     res := res || '.' || ((ip >> 16) & (2^8-1)::int);
  
  
  
  

   
     res := res || '.' || ((ip >> 8) & (2^8-1)::int);
  
  
  
  

   
     res := res || '.' || (ip & (2^8-1)::int);
  
  
  
  

   
     return res;
  
  
  
  

   
   end;
  
  
  
  

   
   $$ language plpgsql;
  
  

测试 : 

  
  

   
   digoal=# select inet_aton('192.168.9.100');
  
  
  
  

   
    inet_aton  
  
  
  
  

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

   
    3232237924
  
  
  
  

   
   (1 row)
  
  
  
  

   
   

  
  
  
  

   
   digoal=# select inet_ntoa(3232237924);
  
  
  
  

   
      inet_ntoa   
  
  
  
  

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

   
    192.168.9.100
  
  
  
  

   
   (1 row)
  
  

在MySQL中假如存储了IP和子网掩码的值, 我们可以通过如下方法来判断IP是否在这个子网中.

1. 从IP和子网掩码运算得到net和broadcast, 然后转换成INT8.

2. 把要匹配的IP转换成INT8, 与第一步得到的值进行比较

例如 : 

  
  

   
   192.168.9.0/24
  
  
  
  

   
   192.168.9.128/25
  
  
  
  

   
   192.168.9.0/25
  
  
  
  

   
   192.168.0.0/16
  
  

转换成net和broadcast

   
   

    
    192.168.9.0, 192.168.9.255
   
   
   
   

    
    192.168.9.128, 192.168.9.255
   
   
   
   

    
    192.168.9.0, 192.168.9.127
   
   
   
   

    
    192.168.0.0, 192.168.255.255
   
   

net的转换方法如下 : 

  
  

   
   digoal=# select substring('192.168.9.0/24','(.*)/\d*');
  
  
  
  

   
     substring  
  
  
  
  

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

   
    192.168.9.0
  
  
  
  

   
   (1 row)
  
  

broadcast的转换方法如下 : 

  
  

   
   digoal=# select inet_ntoa((inet_aton(substring('192.168.9.0/24','(.*)/\d*')) + 2^(32-substring('192.168.9.0/24','.*/(\d*)')::int) -1)::int8);
  
  
  
  

   
      inet_ntoa   
  
  
  
  

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

   
    192.168.9.255
  
  
  
  

   
   (1 row)
  
  

转换成数字就可以做比较了.

  
  

   
   

    
    digoal=# create table t1 (ip text, inet text);
   
   
   
   

    
    CREATE TABLE
   
   
   
   

    
    digoal=# insert into t1 values ('192.168.1.1','192.168.1.128/25');
   
   
   
   

    
    INSERT 0 1
   
   
   
   

    
    digoal=# insert into t1 values ('192.168.1.10','192.168.1.0/25');
   
   
   
   

    
    INSERT 0 1
   
   
   
   

    
    digoal=# insert into t1 values ('192.168.1.129','192.168.1.128/25');
   
   
   
   

    
    INSERT 0 1
   
   
  
  
  
  

   
   

    
    digoal=# select inet, substring(inet,'(.*)/\d*') as net, inet_ntoa((inet_aton(substring(inet,'(.*)/\d*')) + 2^(32-substring(inet,'.*/(\d*)')::int) -1)::int8) as broadcast from t1;
   
   
   
   

    
           inet       |      net      |   broadcast   
   
   
   
   

    
    ------------------+---------------+---------------
   
   
   
   

    
     192.168.1.128/25 | 192.168.1.128 | 192.168.1.255
   
   
   
   

    
     192.168.1.0/25   | 192.168.1.0   | 192.168.1.127
   
   
   
   

    
     192.168.1.128/25 | 192.168.1.128 | 192.168.1.255
   
   
   
   

    
    (3 rows)
   
   
  
  

查看IP是否落在IPNET范围内.

  
  

   
   digoal=# select * from (select ip, inet, substring(inet,'(.*)/\d*') as net, inet_ntoa((inet_aton(substring(inet,'(.*)/\d*')) + 2^(32-substring(inet,'.*/(\d*)')::int) -1)::int8) as broadcast from t1) t where inet_aton(ip) > inet_aton(net) and inet_aton(ip) < inet_aton(broadcast);
  
  
  
  

   
         ip       |       inet       |      net      |   broadcast   
  
  
  
  

   
   ---------------+------------------+---------------+---------------
  
  
  
  

   
    192.168.1.10  | 192.168.1.0/25   | 192.168.1.0   | 192.168.1.127
  
  
  
  

   
    192.168.1.129 | 192.168.1.128/25 | 192.168.1.128 | 192.168.1.255
  
  
  
  

   
   (2 rows)
  
  

[参考]

1.  http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_inet-aton

2.  http://doxygen.postgresql.org/inet__aton_8c_source.html#l00054

3.  http://www.postgresql.org/docs/9.3/static/datatype-net-types.html

4.  http://www.postgresql.org/docs/9.3/static/functions-net.html

5.  http://doxygen.postgresql.org/inet__aton_8c_source.html#l00054