May 2014

Once in a while, you have to juggle around with IP addresses and store them / process them in an efficient way. To do so PostgreSQL provides us with various data types, including cidr and inet. The beauty here is that those two types make sure that no bad data can be inserted into the database:

       test=# SELECT '192.168.0.34567'::inet;
            ERROR:  invalid input syntax for type inet: '192.168.0.34567'
            LINE 1: SELECT '192.168.0.34567'::inet;

test=# SELECT '192.168.0.34567'::inet;

ERROR: invalid input syntax for type inet: '192.168.0.34567'

LINE 1: SELECT '192.168.0.34567'::inet;

However, an IPv4 address is basically just a 4 byte integer, which happens to be displayed in a fairly convenient way. So, why not cast an integer value to an IPv4 address?

       test=# SELECT 43242::int;
             int4 
            -------
             43242
            (1 row)

            test=# SELECT 43242::int::inet;
            ERROR:  cannot cast type integer to inet
            LINE 1: SELECT 43242::int::inet;

test=# SELECT 43242::int;

int4

-------

43242

(1 row)

test=# SELECT 43242::int::inet;

ERROR: cannot cast type integer to inet

LINE 1: SELECT 43242::int::inet;

Unfortunately there is no automatic type cast available to do the job.

Defining custom type casts

This is exactly when CREATE CAST can come to your rescue. In PostgreSQL defining your own type cast is not more than a 5 minute task.

Here is the syntax of CREATE CAST:

test=# h CREATE CAST
Command:     CREATE CAST

Description: define a new cast
Syntax:

CREATE CAST (source_type AS target_type)
    WITH FUNCTION function_name (argument_type [, ...])
    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)
    WITHOUT FUNCTION
    [ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)
    WITH INOUT
    [ AS ASSIGNMENT | AS IMPLICIT ]

test=# h CREATE CAST

Command: CREATE CAST

Description: define a new cast

Syntax:

CREATE CAST (source_type AS target_type)

WITH FUNCTION function_name (argument_type [, ...])

[ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)

WITHOUT FUNCTION

[ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST (source_type AS target_type)

WITH INOUT

[ AS ASSIGNMENT | AS IMPLICIT ]

The clue here is that you can use a normal stored procedure to define a type cast. All you have to do is to write a procedure accepting just one parameter returning the proper value. Here is how it works:

CREATE OR REPLACE FUNCTION int2inet(int) RETURNS inet AS
$$
        SELECT '0.0.0.0'::inet + $1;
$$ LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION int2inet(int) RETURNS inet AS

SELECT '0.0.0.0'::inet + $1;

$$ LANGUAGE 'sql';

First of all we define a simple function. The beauty here is that we can simply add an integer to 0.0.0.0 and return the value. Pretty simple I would say.

Finally we can define the cast ...

CREATE CAST (int AS inet)
        WITH FUNCTION int2inet(int)
        AS IMPLICIT;

CREATE CAST (int AS inet)

WITH FUNCTION int2inet(int)

AS IMPLICIT;

... and enjoy the fruits of our work:

test=# SELECT 314232324::inet;
     inet    
--------------
 18.186.206.4

(1 row)

test=# SELECT 314232324::inet;

inet

--------------

18.186.206.4

(1 row)

Simple casts like that should not take long to implement. However, your life can be a lot easier if you got all the tools in place you will need for your daily work.

For interesting blogs on the topic of PostgreSQL and networks, take a look in our networking blog spot.