/**
 * Creates a view to get information about bloat in tables.
 * The view requires the extension pgstattuple to be installed.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

DO $$
DECLARE
	pg_extension_installed BOOLEAN;
	view_source TEXT;
BEGIN

	SELECT count(*) pgstattuple_exists
	FROM pg_extension
	WHERE extname = 'pgstattuple'
	INTO pg_extension_installed
	;

	IF pg_extension_installed THEN
		-- The view is only created when pgstattuple is installed
		view_source :=
$string$
CREATE OR REPLACE VIEW pg_table_bloat AS
WITH table_list AS
	(
		SELECT t.schemaname AS schema_name
			, t.tablename AS table_name
			, t.tableowner AS table_owner
			, t.schemaname || '.' || t.tablename AS schema_table_name
		FROM pg_catalog.pg_tables AS t
		LEFT OUTER JOIN pg_catalog.pg_partitioned_table AS pt
			ON (t.schemaname || '.' || t.tablename)::regclass::oid = pt.partrelid
		WHERE t.schemaname NOT IN
			(
				'pg_catalog',
				'information_schema'
			)
		AND pt.partrelid IS NULL
	)
SELECT table_list.schema_name
	, table_list.table_name
	, table_list.table_owner
	, st.table_len AS table_length_in_bytes
	, st.tuple_count
	, st.tuple_len AS tuple_length_in_bytes
	, st.tuple_percent
	, st.dead_tuple_count
	, st.dead_tuple_len AS dead_tuple_length_in_bytes
	, st.dead_tuple_percent
	, st.free_space
	, st.free_percent
FROM table_list
CROSS JOIN LATERAL pgstattuple(table_list.schema_table_name) AS st
ORDER BY schema_name , table_name
;
$string$
;
		EXECUTE view_source;

		COMMENT ON VIEW pg_table_bloat IS 'The list of tables and current bloat.';
		COMMENT ON COLUMN pg_table_bloat.schema_name IS 'The name of the schema.';
		COMMENT ON COLUMN pg_table_bloat.table_name IS 'The name of the table.';
		COMMENT ON COLUMN pg_table_bloat.table_owner IS 'The name of the role that owns the table.';
		COMMENT ON COLUMN pg_table_bloat.table_length_in_bytes IS 'The physical table size in bytes.';
		COMMENT ON COLUMN pg_table_bloat.tuple_count IS 'The number of live tuples.';
		COMMENT ON COLUMN pg_table_bloat.tuple_length_in_bytes IS 'The total length of live tuples in bytes.';
		COMMENT ON COLUMN pg_table_bloat.tuple_percent IS 'The percentage of live tuples compared to dead tuples.';
		COMMENT ON COLUMN pg_table_bloat.dead_tuple_count IS 'The number of dead tuples.';
		COMMENT ON COLUMN pg_table_bloat.dead_tuple_length_in_bytes IS 'The total length of dead tuples in bytes.';
		COMMENT ON COLUMN pg_table_bloat.dead_tuple_percent IS 'The percentage of dead tuples compared to live tuples.';
		COMMENT ON COLUMN pg_table_bloat.free_space IS 'The total free space in bytes of this table.';
		COMMENT ON COLUMN pg_table_bloat.free_percent IS 'The percentage of free space of this tuple compared to used space.';

	END IF;

END $$;