Update refresh script to capture performance data on how long each view takes to refresh about cht-app-monitoring-data-ingest HOT 1 CLOSED

DECLARE
  matview RECORD;
  start_time timestamp with time zone;
BEGIN
start_time := transaction_timestamp();
  EXECUTE 'CREATE TABLE IF NOT EXISTS refresh_log(view_name varchar, duration interval, logged_on timestamp with time zone)';
  
  RAISE NOTICE 'Refreshing base metaviews';
  -- other matviews rely on contactview_metadata, which is a matview
  -- so load this first
  REFRESH MATERIALIZED VIEW CONCURRENTLY public.contactview_metadata;
  EXECUTE format('INSERT INTO refresh_log VALUES (''%s'', ''%s''::interval, ''%s'')', 'public.contactview_metadata', clock_timestamp()- start_time, transaction_timestamp());
  FOR matview IN SELECT matviewname, schemaname FROM pg_catalog.pg_matviews LOOP
    IF matview.matviewname = 'contactview_metadata' THEN
      -- this one is already done, skip it.
      CONTINUE;
    END IF;
    RAISE NOTICE 'Refreshing %', matview.matviewname;
    EXECUTE format('REFRESH MATERIALIZED VIEW CONCURRENTLY %s', CONCAT(matview.schemaname, '.', matview.matviewname));
    EXECUTE format('INSERT INTO refresh_log VALUES (''%s'', ''%s''::interval, ''%s'')', CONCAT(matview.schemaname, '.', matview.matviewname), clock_timestamp() - start_time, transaction_timestamp());
  END LOOP;
  RAISE NOTICE 'Materialized views refreshed.';
  RETURN 1;
END;