偶看新闻哺乳期奶头痛的原因:PostgreSQL中二进制转换的例子
来源：百度文库 编辑：偶看新闻 时间：2024/05/09 12:08:54
CREATE OR REPLACE FUNCTION spm.trans_ip_address(a_ip_address text)
RETURNS text AS
$BODY$declare
tmp_ip varchar;
ret_ip varchar;
num1 varchar;
num2 varchar;
num3 varchar;
num4 varchar;
pos smallint;
int1 integer;
int2 integer;
int3 integer;
int4 integer;
begin
tmp_ip := a_ip_address;
pos := strpos(tmp_ip,‘.‘) ;
num1 := substr(tmp_ip,1,pos-1);
tmp_ip := substr(tmp_ip,pos+1);
pos := strpos(tmp_ip,‘.‘) ;
num2:= substr(tmp_ip,1,pos-1);
tmp_ip := substr(tmp_ip,pos+1);
pos := strpos(tmp_ip,‘.‘) ;
num3:= substr(tmp_ip,1,pos-1);
num4 := substr(tmp_ip,pos+1);
int1 := num1;
int2 := num2;
int3 := num3;
int4 := num4;
select cast((int1::bit(64)<<24)|(int2::bit(64)<<16)|(int3::bit(64)<<8)|(int4::bit(64)) as text) into ret_ip;
return ret_ip;
end;$BODY$
LANGUAGE ‘plpgsql‘ VOLATILE