PostgreSQL: iterate over all tables and calculate the avg for a given column

CREATE OR REPLACE FUNCTION avg_vms_day() RETURNS TABLE(pdate TEXT, ptime TEXT, pamount INT) AS $$
DECLARE
	  tbl_name text;
	  total_vms int;
	  date_collected text;
	  time_collected text;
BEGIN
	DROP TABLE IF EXISTS amount_vms_per_day;
	CREATE TABLE amount_vms_per_day (pdate TEXT, ptime TEXT, pamount INT);
    FOR tbl_name IN (SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_name LIKE 'all_vms_2021%') 
		LOOP
	    EXECUTE 'SELECT COUNT(vm_id) FROM ' || tbl_name INTO STRICT total_vms;
			date_collected := split_part(tbl_name, '_', 3);
			time_collected := split_part(tbl_name, '_', 4);
			INSERT INTO amount_vms_per_day VALUES (date_collected,time_collected,total_vms);
    END LOOP;
	RETURN QUERY EXECUTE 'SELECT * FROM amount_vms_per_day';
END;
$$ LANGUAGE plpgsql;

select pdate,round(AVG(pamount),0) from avg_vms_day() GROUP BY pdate;

Advertisement