Loop over PostgreSQL Tables

The following script loops over some PostgreSQL tables and grabs row counts

-- 1) Where results will go
CREATE TEMP TABLE tmp_table_stats (
  schema_name      text,
  table_name       text,
  row_count        bigint,
  lastupdated_max  timestamptz,
  count_method     text  -- 'exact' (this script) or 'estimate' (alternative below)
);

-- 2) Gather stats for all non-system tables (exact counts)
DO $$
DECLARE
  r           RECORD;
  colname     text;
  cnt         bigint;
  max_ts      timestamptz;
BEGIN
  FOR r IN
    SELECT
      n.nspname AS schema_name,
      c.relname AS table_name,
      c.oid     AS relid,
      quote_ident(n.nspname) AS qi_schema,
      quote_ident(c.relname) AS qi_table
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind = 'r'                    -- ordinary tables
      AND n.nspname NOT IN ('pg_catalog','information_schema','pg_toast')
    ORDER BY 1, 2
  LOOP
    -- exact row count
    EXECUTE format('SELECT count(*) FROM %s.%s', r.qi_schema, r.qi_table)
      INTO cnt;

    -- find a column named lastUpdated / lastupdated (quoted vs unquoted)
    SELECT a.attname
      INTO colname
    FROM pg_attribute a
    WHERE a.attrelid = r.relid
      AND NOT a.attisdropped
      AND lower(a.attname) = 'lastupdated'
    LIMIT 1;

    IF colname IS NOT NULL THEN
      EXECUTE format('SELECT max(%s) FROM %s.%s',
                     quote_ident(colname), r.qi_schema, r.qi_table)
        INTO max_ts;
    ELSE
      max_ts := NULL;
    END IF;

    INSERT INTO tmp_table_stats(schema_name, table_name, row_count, lastupdated_max, count_method)
    VALUES (r.schema_name, r.table_name, cnt, max_ts, 'exact');
  END LOOP;
END
$$;