duckdb で CIDR の範囲のIPアドレスを列挙したい

cidr_range.sql

with
  t1 as (
    select
      -- '172.23.48.64/26' as cidr,
      '172.23.48.64/22' as cidr,
      split(cidr, '/') as part1,
      split(part1[1], '.') as octets,
      part1[2]::uint32 as prefixLength,
      1 << (32 - prefixLength) as addrNum,
      xor (-1::uint32, (1 << (32 - prefixLength)) -1) as subnetMask,
      list_reduce(
        list_transform(
          list_zip(octets, [24, 16, 8, 0]),
          lambda x: x[1]::uint32 * (1 << x[2])
        ),
        lambda acc,
        x: acc + x,
        0
      ) as cidrBaseIp,
      cidrBaseIp & subnetMask as fromRange,
      fromRange + addrNum as toRange,
  ),
  t2 as (
    select
      *,
      list_aggregate(
        [
          255 & (s.seq >> 24),
          255 & (s.seq >> 16),
          255 & (s.seq >> 8),
          255 & (s.seq >> 0),
        ],
        'string_agg',
        '.'
      ) as ipAddr
    from
      t1
      JOIN LATERAL UNNEST(range(fromRange, toRange)) AS s (seq) ON TRUE
  )
select
  *
from
  t2

cidr_range_inet.sql

-- inet 使用

WITH
  start_end AS (
    SELECT
      '172.23.48.64/22' AS cidr,
      network(cidr)   AS start_inet,
      broadcast(cidr) AS end_inet,
      list_reduce(
        list_transform(
          list_zip(split(host(start_inet), '.'), [24, 16, 8, 0]),
          lambda x: x[1]::uint32 * (1 << x[2])
        ),
        lambda acc,
        x: acc + x,
        0
      ) as start_inet_u32,
      list_reduce(
        list_transform(
          list_zip(split(host(end_inet), '.'), [24, 16, 8, 0]),
          lambda x: x[1]::uint32 * (1 << x[2])
        ),
        lambda acc,
        x: acc + x,
        0
      ) as end_inet_u32,
      end_inet_u32 - start_inet_u32 as size
  ),
  t2 as (
SELECT
  *,
  host(start_inet)::inet + seq as ipAddr
from
start_end
JOIN LATERAL UNNEST(generate_series(0, size)) AS s (seq) ON TRUE
)

select
cidr, ipAddr
from t2