CREATE OR REPLACE FUNCTION get_top_itemids_history_uint()
RETURNS TABLE (itemid bigint, key varchar(2048), hosts varchar(2048), cnt bigint ) AS $$
DECLARE
last_chunk text;
chunk_schema text := '_timescaledb_internal';
BEGIN
SELECT chunk_name
INTO last_chunk
FROM timescaledb_information.chunks
WHERE hypertable_name = 'history_uint'
ORDER BY range_end_integer DESC
LIMIT 1;

RETURN QUERY EXECUTE format(
    'SELECT
        i.itemid,
        i.key_,
        h.host,
        COUNT(*) AS cnt
    FROM
        _timescaledb_internal.%I AS hu
        JOIN items i ON hu.itemid = i.itemid
        JOIN hosts h ON i.hostid = h.hostid
    GROUP BY
        i.itemid, i.key_, h.host
    ORDER BY
        cnt DESC
    LIMIT 10', last_chunk);

END;
$$ LANGUAGE plpgsql;

-- Appeler la fonction
SELECT * FROM get_top_itemids_history_uint();

CREATE OR REPLACE FUNCTION get_top_itemids_history()
RETURNS TABLE (itemid bigint, key varchar(2048), hosts varchar(2048), cnt bigint ) AS $$
DECLARE
last_chunk text;
chunk_schema text := '_timescaledb_internal';
BEGIN
SELECT chunk_name
INTO last_chunk
FROM timescaledb_information.chunks
WHERE hypertable_name = 'history'
ORDER BY range_end_integer DESC
LIMIT 1;

RETURN QUERY EXECUTE format(
    'SELECT
        i.itemid,
        i.key_,
        h.host,
        COUNT(*) AS cnt
    FROM
        _timescaledb_internal.%I AS hu
        JOIN items i ON hu.itemid = i.itemid
        JOIN hosts h ON i.hostid = h.hostid
    GROUP BY
        i.itemid, i.key_, h.host
    ORDER BY
        cnt DESC
    LIMIT 10', last_chunk);

END;
$$ LANGUAGE plpgsql;

-- Appeler la fonction
SELECT * FROM get_top_itemids_history();