Table looping query

query.sql

-- Create a temporary table with a JSON column for grouping variables.
CREATE TEMP TABLE temp_counts (
    group_vars   json,      -- holds grouping variables (e.g., calc_date, formatted_date, etc.)
    count_result integer
) ON COMMIT DROP;

DO $$
DECLARE
    begin_date      date := '2023-01-01';  -- Replace with your desired begin date.
    end_date        date := '2023-01-10';    -- Replace with your desired end date.
    v_current_date  date;
    count_result    integer;
    iteration       integer := 0;
    total_days      integer;
    iteration_start timestamp;
    iteration_end   timestamp;
    query_time      numeric;
    total_query_time numeric := 0;
BEGIN
    total_days := end_date - begin_date + 1;
    v_current_date := begin_date;
    
    WHILE v_current_date <= end_date LOOP
        iteration := iteration + 1;
        iteration_start := clock_timestamp();
        
        -- Query the calculation table for the count of records for the current date.
        SELECT COUNT(*) INTO count_result
        FROM calculation
        WHERE calc_dt = to_char(v_current_date, 'YYYYMMDD');
        
        iteration_end := clock_timestamp();
        query_time := EXTRACT(EPOCH FROM (iteration_end - iteration_start));
        total_query_time := total_query_time + query_time;
        
        -- Insert the result into the temporary table with grouping variables stored as JSON.
        INSERT INTO temp_counts (group_vars, count_result)
        VALUES (
            json_build_object(
                'calc_date', v_current_date,
                'formatted_date', to_char(v_current_date, 'YYYYMMDD')
                -- add additional grouping fields here as needed
            ),
            count_result
        );
        
        -- Log progress with a timestamp (mm:ss), iteration details, and query duration in seconds.
        RAISE NOTICE '[%] Progress: %/% - Processed date %; Query time: % seconds', 
            to_char(clock_timestamp(), 'MI:SS'),
            iteration,
            total_days,
            v_current_date,
            round(query_time, 2);
        
        -- Move to the next day.
        v_current_date := v_current_date + INTERVAL '1 day';
    END LOOP;
    
    -- Log overall summary: total query time and average query time per iteration.
    RAISE NOTICE 'Overall query time: % seconds; Average query time: % seconds over % iterations', 
        round(total_query_time, 2),
        round(total_query_time / iteration, 2),
        iteration;
END $$;

-- Final select that extracts the individual grouping values from the JSON column.
SELECT 
    group_vars ->> 'calc_date' AS calc_date,
    group_vars ->> 'formatted_date' AS formatted_date,
    count_result
FROM temp_counts;