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会比较愉快.

例如

>>containsinet '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