PostgreSQL function to calculate the threadHash or root_parent_hash for all casts

insert_root_parent_cast_and_return_count

DECLARE
    rows_inserted INTEGER;
BEGIN

    WITH RECURSIVE root_mapping AS (
        -- Base case: Casts without a parent and with a timestamp in the last hour
        SELECT hash, timestamp, hash AS root_parent_hash
        FROM casts
        WHERE parent_hash IS NULL
        AND timestamp > NOW() - INTERVAL '1 hour'

        UNION ALL

        -- Recursive step: Casts that are descendants of our base case
        SELECT c.hash, c.timestamp, rm.root_parent_hash
        FROM casts c
        JOIN root_mapping rm ON c.parent_hash = rm.hash
        WHERE c.timestamp > NOW() - INTERVAL '1 hour'
    )

    INSERT INTO casts_with_root_parent_hash(hash, root_parent_hash, timestamp)
    SELECT hash, root_parent_hash, timestamp
    FROM root_mapping
    WHERE hash != root_parent_hash
    ON CONFLICT (hash) DO NOTHING;

    -- Get the number of rows inserted
    GET DIAGNOSTICS rows_inserted = ROW_COUNT;

    RETURN QUERY SELECT rows_inserted;

END;