[PostgreSQL] IP주소 변환 ( int to string, string to int )

1) Integer 형 IP주소를 varchar로 변환 (int to string)

예)  -1062731420 --> '192.168.1.100'

DROP FUNCTION IF EXISTS my_db.fn_ipnum2str(var_int_ip INTEGER);

CREATE FUNCTION my_db.fn_ipnum2str(var_int_ip INTEGER)

  RETURNS CHARACTER VARYING AS $$

BEGIN

RETURN ((var_int_ip>>24&255)||'.'||(var_int_ip>>16&255)||'.'||(var_int_ip>>8&255)||'.'||(var_int_ip>>0&255));

END;

$$ LANGUAGE plpgsql;

2) Varchar 형 IP주소를 integer 형으로 변환 (string to int)

예) '192.168.1.100' --> -1062731420

DROP FUNCTION IF EXISTS my_db.fn_ipstr2num(CHARACTER VARYING);

CREATE FUNCTION my_db.fn_ipstr2num(var_str_ip CHARACTER VARYING)

RETURNS INTEGER AS $$

DECLARE

var_bigint_ip BIGINT DEFAULT 0;

var_result INT DEFAULT 0;

BEGIN

-- ip string to bigint (int8)

var_bigint_ip = CAST(var_str_ip AS INET) - '0.0.0.0'::INET;

-- bigint (int8) to int (int4) : bigint -> hex -> int

EXECUTE 'SELECT x''' || to_hex(var_bigint_ip) || '''::int' INTO var_result;

RETURN var_result;

END; 

$$ LANGUAGE plpgsql