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, 29 Jan 2005 22:24:46 +0300, Andrey V. Semyonov <wilfre@mail.ru> wrote:
> 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).
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

First of all you need a way to convert bigint to inet, say, something
like:

CREATE OR REPLACE FUNCTION bigint_to_inet(i bigint) RETURNS inet AS $$
   BEGIN RETURN ((i / 16777216)||'.'||(i % 16777216 / 65536)||'.'||(i
% 65536 / 256)||'.'||(i % 256))::inet; END;
$$ LANGUAGE plpgsql IMMUTABLE;

then create a sequence:

CREATE SEQUENCE foo_inetaddrs_seq START 192*16777216::bigint+168*65536;
CREATE TABLE foo (
     inetaddrs inet default
bigint_to_inet(netval('foo_inetaddrs_seq')) PRIMARY KEY,
---     rest with whatever...
);

Something like this?

On Sat, Jan 29, 2005 at 22:24:46 +0300,
  "Andrey V. Semyonov" <wilfre@mail.ru> wrote:
> 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).

Unless you have a huge net block, you probably don't want to use a sequence
like object anyway. You are going to want some way to reuse previous IP
addresses that have been released.

Having a table of usable IP addresses with a flag indicating which are in
use might be a practical solution for you if the block(s) of addresses
isn't too big.