A postgresql inet to ip6.arpa snippet

[Martin Millnert]

128-bit friends,

I just took the time to come up with a IMHO quite clean way to create
ip6.arpa strings from inet types in PostgreSQL and thought I'd share (if
nothing else so for the Internet archives, where I came up empty with a
similar solution previously):

Prerequisites: a string reverse() function. Either, wait for/install 9.2
where it seems to be included, or simply install for example this one:
http://archives.postgresql.org/pgsql-sql/2005-02/msg00334.php

Then:
sysdb=# SELECT
reverse(regexp_replace(substring(encode(inet_send('2a02:9a0:100:2::101'::inet), 'hex') from 9), '(.)', E'.\\1', 'g'));
                             reverse                              
------------------------------------------------------------------
 1.0.1.0.0.0.0.0.0.0.0.0.0.0.0.0.2.0.0.0.0.0.1.0.0.a.9.0.2.0.a.2.
(1 row)

It may not look as much, but for me the dark magic was the inet_send
function to access the raw data of the inet type. I'm using this myself
now to generate part of a zone file anyway.

Cheers,
Martin

(If you're not using pgsql already, "you're doing it wrong" ;-) )