IP increment function in PL/pgSQL

Question

I need a PL/pgSQL function that takes an IP address as text and adds a given integer value to it, carrying over the octets as needed.

I have a working version of this written in JavaScript already:

export const incrementIpAddress = (ipAddress: string, amount: number) => {
    const octets = ipAddress.split('.');
    const numbers = octets.map(octet => parseInt(octet));

    let carry = amount;
    for(let i = numbers.length - 1; i >= 0 && carry > 0; i--) {
        const sum = numbers[i] + carry;
        numbers[i] = sum % 256;
        carry = Math.floor(sum / 256);
    }

    return numbers.join('.');
}

I tried having ChatGPT convert this into PL/pgSQL (I know, I'm sorry), and this is what it came up with:

CREATE OR REPLACE FUNCTION increment_ip_address(ip_address text, amount integer) RETURNS text AS $$
DECLARE
    octets text[];
    numbers integer[];
    carry integer;
    i integer;
BEGIN
    octets := string_to_array(ip_address, '.');
    FOREACH i IN ARRAY octets LOOP
        numbers := numbers || i::integer;
    END LOOP;

    carry := amount;
    FOR i IN REVERSE 1 .. array_upper(numbers, 1) LOOP
        numbers[i] := numbers[i] + carry;
        carry := FLOOR(numbers[i] / 256);
        numbers[i] := numbers[i] % 256;
    END LOOP;

    RETURN array_to_string(numbers, '.')::text;
END;
$$ LANGUAGE plpgsql;

However this doesn't work and always outputs the same IP it was given, not incremented. Can I get some help making this function work?

Answer 1

PostgreSQL has a built-in inet type you can use for exactly that purpose. It works with a + operator as you'd expect, adding with a proper carry-over behaviour: demo

select '127.0.0.255'::inet+23;
--127.0.1.22