Re: inet-type sequence

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/