Thread: inet-type sequence

Hi there.
How do I create a sequence of type inet for automatic assignment an
IP/32 to a new row?
Neither nextval() nor CREATE SEQUENCE seem for me to work with type inet
(PostgreSQL 8.0.0, pgAdmin III v 1.2.0).

On Sat, Jan 29, 2005 at 10:40:56PM +0300, Andrey V. Semyonov wrote:

> How do I create a sequence of type inet for automatic assignment an
> IP/32 to a new row?

Sequences values are bigints, so you'd have to convert a bigint to
inet or cidr.  I don't see any built-in casts between numeric
types and network address types, but you can cast a hex string
to cidr (but not inet?):

SELECT cidr'0x01020304';
    cidr
------------
 1.2.3.4/32
(1 row)

Here's an idea:

CREATE FUNCTION bigint2inet(bigint) RETURNS inet AS '
BEGIN
    RETURN cidr(''0x'' || lpad(to_hex($1), 8, ''0''));
END;
' LANGUAGE plpgsql IMMUTABLE STRICT;

CREATE SEQUENCE addrseq START WITH 3232235777;  -- 192.168.1.1

CREATE TABLE foo (
    addr  inet NOT NULL DEFAULT bigint2inet(nextval('addrseq'))
);

INSERT INTO foo VALUES (DEFAULT);
INSERT INTO foo VALUES (DEFAULT);
INSERT INTO foo VALUES (DEFAULT);

SELECT * FROM foo;
    addr
-------------
 192.168.1.1
 192.168.1.2
 192.168.1.3
(3 rows)

Remember that sequences don't roll back, so you could end up with
gaps:

BEGIN;
INSERT INTO foo VALUES (DEFAULT);
ROLLBACK;
INSERT INTO foo VALUES (DEFAULT);
SELECT * FROM foo;
    addr
-------------
 192.168.1.1
 192.168.1.2
 192.168.1.3
 192.168.1.5
(4 rows)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Sat, Jan 29, 2005 at 02:35:06PM -0700, Michael Fuhr wrote:

> CREATE FUNCTION bigint2inet(bigint) RETURNS inet AS '
> BEGIN
>     RETURN cidr(''0x'' || lpad(to_hex($1), 8, ''0''));
> END;
> ' LANGUAGE plpgsql IMMUTABLE STRICT;

I should point out that the above function is intended only as a
trivial example.  It's designed for IPv4 address and will have
problems when the bigint value exceeds 2^32 - 1 (4294967295):

SELECT bigint2inet((2^32 - 1)::bigint);
   bigint2inet
-----------------
 255.255.255.255
(1 row)

SELECT bigint2inet((2^32)::bigint);
 bigint2inet
-------------
 16.0.0.0
(1 row)

A more robust implementation is left as an exercise for the reader.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/