mac到sn转换sql 脚本

--16进制转10进制

CREATE OR REPLACE FUNCTION hex_to_int(hexval varchar) RETURNS bigint AS $$
DECLARE
    result  bigint;
BEGIN
    EXECUTE 'SELECT x' || quote_literal(hexval) || '::bigint' INTO result;
    RETURN result;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

--allMac转allEsn

CREATE OR REPLACE FUNCTION getallesn(sn varchar(100))
  RETURNS varchar(100)
  LANGUAGE plpgsql
AS
$$
declare
  resn varchar(100);
  arrsn varchar(100) [];
  s varchar(10);
  x varchar(1);
  y varchar(1);
  i int;
  j int;
begin
  resn='';
  arrsn =regexp_split_to_array(sn,':');
  <<label1>> foreach s  in array arrsn
  loop
    x=substring(s,1,1);
y=substring(s,2,1);
--i
if x<>' ' then
i=hex_to_int(x); 
if i%2=0 then 
i=i+1;
else 
i=i-1;
end if;
end if;
--j
if y<>' ' then
j=hex_to_int(y); 
if j%2=0 then 
j=j+1;
else 
j=j-1;
end if;
end if;
    resn=resn||to_hex(i)||to_hex(j)||':';
  end loop label1;
  resn=substring(resn,1,length(resn)-1);
  return resn;
end;
 
$$;

--mac转esn

CREATE OR REPLACE FUNCTION public.getesn(
sn character varying)
    RETURNS character varying
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
declare
  resn varchar(100);
  arrsn varchar(100) [];
  s varchar(10);
  x varchar(1);
  y varchar(1);
  i int;
  j int;
begin
  resn='';
  arrsn =regexp_split_to_array(sn,':');
  <<label1>> foreach s  in array arrsn
  loop
    x=substring(s,1,1);
y=substring(s,2,1);
--i
if x<>' ' then
i=hex_to_int(x); 
if i%2=0 then 
i=i+1;
else 
i=i-1;
end if;
else
x='0';
i=hex_to_int(x); 
if i%2=0 then 
i=i+1;
else 
i=i-1;
end if;
end if;
--j
if y<>' ' then
j=hex_to_int(y); 
if j%2=0 then 
j=j+1;
else 
j=j-1;
end if;
else 
y='0';
j=hex_to_int(y); 
if j%2=0 then 
j=j+1;
else 
j=j-1;
end if;
end if;
    resn=resn||to_hex(i)||to_hex(j)||':';
  end loop label1;
  resn=substring(resn,1,length(resn)-1);
  return resn;
end;
$BODY$;

-------------- 阿里环境mac处理转换

--esn 赋值到mac
update ne set extattrs=jsonb_set(extattrs, '{mac}', concat('"', extattrs->>'esn', '"')::jsonb,true) where extattrs->>'esn' is not null;
--allEsn 赋值到allMac
update ne set extattrs=jsonb_set(extattrs, '{allMac}', concat('"', extattrs->>'allEsn', '"')::jsonb,true) where extattrs->>'allMac' is not null;
--从allMac转换到allEsn  偶数+1  奇数-1
update ne set extattrs=jsonb_set(extattrs, '{allEsn}', concat('"', getallesn(extattrs->>'allMac'), '"')::jsonb,true) where extattrs->>'allEsn' is not null; 
--从mac转换到esn  偶数+1  奇数-1
update ne set extattrs=jsonb_set(extattrs, '{esn}', concat('"', getesn(extattrs->>'mac'), '"')::jsonb,true) where extattrs->>'allEsn' is not null;

--给cpe设备 snOrMac 赋值默认值 为mac
update ne set extattrs=jsonb_set(extattrs, '{snOrMac}', concat('"', 'mac', '"')::jsonb,true)  where 
 connectiontypes->0=('"CALLHOME"')::jsonb and extattrs ->>'snOrMac' is null;