On Aug 23, 2013 11:58 AM, "Mikhail Puzanov" <misha.puzanov@gmail.com> wrote:
>
> Hello,
>
> I need to convert the set of ipv6 addresses stored as numerics
> (by historical reasons mostly) to inet type.
>
> Something like
> '0:0:0:0:0:0:0:0'::inet + 55831599345971591062080247067748335616::bigint
> apparently doesn't work as the number added is too big to be converted to bigint.
>
> Is there any reasonable way to do it in sql/pgplsql?
The only language I know that can easily work with such large ints is python. The only way to get the number to python is as text:
arjen=# create or replace function to_inet_ipv6(n varchar) returns inet language plpythonu as $$
import re
return re.sub('(....)', r'\1:', '%032x' % int(n))[:-1]
$$;
CREATE FUNCTION
arjen=# select to_inet_ipv6(55831599345971591062080247067748335615::varchar);
to_inet_ipv6
-----------------------------------------
2a00:c65f:ffff:ffff:ffff:ffff:ffff:ffff
(1 row)
Groeten,
Arjen
>
> Thanks in advance.
>
> --
> Best regards,
> Mikhail V. Puzanov.
