Обсуждение: inet to bigint?
OK, I give up - how do I convert an INET type to a NUMERIC representation of its network address? Is there a quick and easy way? Chris
On Tue, Dec 06, 2005 at 03:31:59PM +0800, Christopher Kings-Lynne wrote:
> OK, I give up - how do I convert an INET type to a NUMERIC
> representation of its network address?
How about:
CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$
use Socket;
return unpack("N", inet_aton($_[0]));
$$ LANGUAGE plperlu IMMUTABLE STRICT;
SELECT inet2num('127.0.0.1'); inet2num
------------2130706433
(1 row)
--
Michael Fuhr
On 12/6/05, Michael Fuhr <mike@fuhr.org> wrote:
you can use this one:
... AS $$
return unpack("N", pack("C4", split(/\./, $_[0])));
$$ language plperl IMMUTABLE STRICT;
to avoid the need to use untrusted languages.
it is less readable thought :(
depesz
How about:
CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$
use Socket;
return unpack("N", inet_aton($_[0]));
$$ LANGUAGE plperlu IMMUTABLE STRICT;
you can use this one:
... AS $$
return unpack("N", pack("C4", split(/\./, $_[0])));
$$ language plperl IMMUTABLE STRICT;
to avoid the need to use untrusted languages.
it is less readable thought :(
depesz
PL/SQL or PL/PGSQL...
Chris
Michael Fuhr wrote:
> On Tue, Dec 06, 2005 at 03:31:59PM +0800, Christopher Kings-Lynne wrote:
>
>>OK, I give up - how do I convert an INET type to a NUMERIC
>>representation of its network address?
>
>
> How about:
>
> CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$
> use Socket;
> return unpack("N", inet_aton($_[0]));
> $$ LANGUAGE plperlu IMMUTABLE STRICT;
>
> SELECT inet2num('127.0.0.1');
> inet2num
> ------------
> 2130706433
> (1 row)
>
On Tue, Dec 06, 2005 at 03:51:17PM +0800, Christopher Kings-Lynne wrote: > PL/SQL or PL/PGSQL... Sheesh, arbitrary restrictions ;-) Something like this then: CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$ DECLARE a text[] := string_to_array(host($1), '.'); BEGIN RETURN a[1]::numeric * 16777216 + a[2]::numeric * 65536 + a[3]::numeric * 256 + a[4]::numeric; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; -- Michael Fuhr
> Sheesh, arbitrary restrictions ;-) Something like this then: > > CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$ > DECLARE > a text[] := string_to_array(host($1), '.'); > BEGIN > RETURN a[1]::numeric * 16777216 + > a[2]::numeric * 65536 + > a[3]::numeric * 256 + > a[4]::numeric; > END; > $$ LANGUAGE plpgsql IMMUTABLE STRICT; Cool, and now the reverse? :D (I'll credit you in the MySQL Compat Library code btw) If you're interested, you'd be welcome to join the project btw... Chris
On Tue, Dec 06, 2005 at 01:05:12AM -0700, Michael Fuhr wrote: > CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$ > DECLARE > a text[] := string_to_array(host($1), '.'); > BEGIN > RETURN a[1]::numeric * 16777216 + > a[2]::numeric * 65536 + > a[3]::numeric * 256 + > a[4]::numeric; > END; > $$ LANGUAGE plpgsql IMMUTABLE STRICT; I should point out that this is only for IPv4, so a family() check might be in order. -- Michael Fuhr
On Tue, Dec 06, 2005 at 04:10:22PM +0800, Christopher Kings-Lynne wrote: > >Sheesh, arbitrary restrictions ;-) Something like this then: > > > >CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$ > >DECLARE > > a text[] := string_to_array(host($1), '.'); > >BEGIN > > RETURN a[1]::numeric * 16777216 + > > a[2]::numeric * 65536 + > > a[3]::numeric * 256 + > > a[4]::numeric; > >END; > >$$ LANGUAGE plpgsql IMMUTABLE STRICT; > > Cool, and now the reverse? :D Tom posted one just a couple of days ago: http://archives.postgresql.org/pgsql-general/2005-12/msg00191.php > (I'll credit you in the MySQL Compat Library code btw) If you're > interested, you'd be welcome to join the project btw... I haven't been following it but I might have some time. Is there a TODO list? The one I see on pgfoundry is empty. -- Michael Fuhr