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/
