PostgreSQL中二进制转换的例子

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