postgres11.2非常好用的ip范围查询函数

创建函数

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;

查询

select * from event where inet_aton(ip)>=inet_aton(‘192.168.1.1’) and inet_aton(ip) <= inet_aton(‘192.168.16.10’);