Даниил Паклин

Если только такой)

create function test.find_islands_in_rental(p_customer_id bigint)
    returns table
            (
                island_id       bigint,
                start_date_time timestamp,
                end_date_time   timestamp
            )
    language plpgsql
as
$$
declare
    cur_row record;
    res_row record;
begin
    drop table if exists temp_table;
    create temporary table temp_table as
    select row_number() over (order by t.rental_date_time, t.return_date_time) as row_number,
           t.rental_date_time                                                  as start_date_time,
           t.return_date_time                                                  as end_date_time
    from test.rental t
    where t.customer_id = p_customer_id
    order by row_number;

    drop table if exists result;
    create temporary table result
    (
        island_id       bigserial,
        start_date_time timestamp,
        end_date_time   timestamp
    );

    for cur_row in select * from temp_table
        loop
            if ((select count(1) from result) = 0)
            then
                insert into result (start_date_time, end_date_time)
                values (cur_row.start_date_time, cur_row.end_date_time);
            else
                select *
                into res_row
                from result
                order by island_id desc
                limit 1;

                if (cur_row.start_date_time <= res_row.end_date_time)
                then
                    update result rr
                    set end_date_time = greatest(cur_row.end_date_time, res_row.end_date_time)
                    where rr.island_id = res_row.island_id;
                else
                    insert into result (start_date_time, end_date_time)
                    values (cur_row.start_date_time, cur_row.end_date_time);
                end if;
            end if;
        end loop;

    return query select * from result;
end;
$$;

Не охватывает случаи, когда на протяжении одного большого периода аренды происходят несколько маленьких: