postgres 获取IPv4 的无符号字符串
对于 IPv4 地址。人们经常使用VARCHAR(15)列去存储IP地址。然而,它们实际上是32位无符号整数,不是字符串。
create function inet_aton(f_address character varying) returns bigint
language plpgsql
as
$$
declare
o_address_number bigint;
begin
SELECT (regexp_replace(f_address, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\1'))::bigint * 16777216 +
(regexp_replace(f_address, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\2'))::bigint * 65536 +
(regexp_replace(f_address, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\3'))::bigint * 256 +
(regexp_replace(f_address, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\4'))::bigint
INTO o_address_number;
RETURN o_address_number;
END;
$$;
alter function inet_aton(varchar) owner to postgres;