get_table_metrics.sql

drop function get_table_metrics CASCADE;
drop type operable_table_metrics CASCADE;

CREATE TYPE operable_table_metrics AS (what text, bytes bigint, bytes_pretty text, per_row_bytes bigint);

CREATE OR REPLACE FUNCTION get_table_metrics(tname text)
RETURNS SETOF operable_table_metrics
LANGUAGE plpgsql
STABLE
STRICT
AS
$$
DECLARE
  dynsql text;
BEGIN
  SELECT INTO dynsql
  'WITH x AS (' ||
  '  SELECT count(*)                AS ct,' ||
  '       sum(length(t::text))    AS txt_len, ' ||
  '       ''' || tname || '''::regclass as tbl ' ||
  ' FROM ' || tname || ' t' ||
  ' ),' ||
  ' y AS (' ||
  ' SELECT ARRAY [' ||
  '          pg_relation_size(tbl),' ||
  '          pg_relation_size(tbl, ''vm''),' ||
  '          pg_relation_size(tbl, ''fsm''),' ||
  '          pg_table_size(tbl),' ||
  '          pg_indexes_size(tbl),' ||
  '          pg_total_relation_size(tbl),' ||
  '          txt_len' ||
  '       ] AS val,' ||
  '       ARRAY [' ||
  '          ''core_relation_size'',' ||
  '          ''visibility_map'',' ||
  '          ''free_space_map'',' ||
  '          ''table_size_incl_toast'',' ||
  '          ''indexes_size'',' ||
  '          ''total_size_incl_toast_and_indexes'',' ||
  '          ''live_rows_in_text_representation'' ' ||
  '       ] AS name ' ||
  ' FROM   x' ||
  ')' ||
  '  SELECT unnest(name)                AS what,' ||
  '     unnest(val)                 AS bytes,' ||
  '     pg_size_pretty(unnest(val)) AS bytes_pretty,' ||
  '     unnest(val) / ct            AS per_row_bytes' ||
  '  FROM   x,y; ';
  RETURN QUERY EXECUTE dynsql;
END
$$;