zAwnxKFw

1DO
2$$
3DECLARE row_data pg_catalog.pg_class%ROWTYPE;
4BEGIN
5EXECUTE 'CREATE TEMP TABLE IF NOT EXISTS fragmentation_results(schema text, relname
6text, total_space bigint, live_space bigint, free_space bigint, fragmentation float)';
7EXECUTE 'TRUNCATE fragmentation_results';
8FOR row_data IN
9     SELECT *
10     FROM pg_class
11     WHERE relkind = 'r' and relpersistence = 'p' and relnamespace IN
12     (select oid from pg_namespace where nspname <> ALL (ARRAY['information_schema'::name]))
13LOOP
14     EXECUTE format('INSERT INTO fragmentation_results SELECT ''%I'' AS schema, '''|| row_data.relname ||''' AS relname, count(blkno) * 8168 AS total_space, (count(blkno) * 8168) - sum(avail) AS live_space, sum(avail) AS free_space, sum(avail)/(count(blkno) * 8168)::float * 100 AS "fragmentation" FROM pg_freespace(
15     ''%I.%I'')', row_data.relnamespace::regnamespace, row_data.relnamespace::regnamespace, row_data.relname);
16END LOOP;
17END$$;