with addr as 
(select lpad(regexp_split_to_table(replace('a::3fff', '::',':ZZZZ:'), ':'),4,'0') saddr )
select trim(regexp_replace(upper(string_agg(s1.forconcat,'')),'(.{4})+?','\1:', 'g' ),':')
from
(select 
   case 
     when saddr = 'ZZZZ'
         then lpad(''::varchar,32 - (select count(*) cnt from addr where saddr <> 'ZZZZ')::Integer * 4,'0')
     else saddr 
   end forconcat
from addr) as s1