/**
 * Creates a view to get information about table and materialized views in the
 * current database. It includes their sizes and indexes.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

CREATE OR REPLACE VIEW pg_table_matview_infos AS
WITH indexes AS
	(
		SELECT schemaname
			, tablename
			, array_agg(indexname) AS indexes
		FROM pg_indexes
		GROUP BY schemaname
			, tablename
	)
SELECT 'table' AS type
	, n.nspname AS schema_name
	, c.relname AS object_name
	, pg_get_userbyid (c.relowner) AS object_owner
	, t.spcname AS TABLESPACE
	, i.indexes
	, pg_table_size (c.oid) AS object_size
	, pg_indexes_size(c.oid) AS indexes_size
	, pg_total_relation_size(c.oid) AS total_object_size
	, pg_size_pretty(pg_table_size(c.oid)) AS total_object_size_pretty
	, pg_size_pretty(pg_indexes_size(c.oid)) AS indexes_size_pretty
	, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_relation_size_pretty
FROM pg_class AS c
	LEFT OUTER JOIN pg_namespace AS n
		ON n.oid = c.relnamespace
	LEFT OUTER JOIN pg_tablespace AS t
		ON t.oid = c.reltablespace
	LEFT OUTER JOIN indexes AS i
		ON n.nspname = i.schemaname
		AND c.relname = i.tablename
WHERE c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])
	AND n.nspname NOT IN ('pg_catalog', 'information_schema')
UNION ALL
SELECT 'matview' AS type
	, n.nspname AS schema_name
	, c.relname AS object_name
	, pg_get_userbyid(c.relowner) AS object_owner
	, t.spcname AS tablespace
	, i.indexes
	, pg_table_size(c.oid) AS object_size
	, pg_indexes_size(c.oid) AS indexes_size
	, pg_total_relation_size(c.oid) AS total_object_size
	, pg_size_pretty(pg_table_size(c.oid)) AS object_size_pretty
	, pg_size_pretty(pg_indexes_size(c.oid)) AS indexes_size_pretty
	, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_relation_size_pretty
FROM pg_class AS c
	LEFT OUTER JOIN pg_namespace AS n
		ON n.oid = c.relnamespace
	LEFT OUTER JOIN pg_tablespace t
		ON t.oid = c.reltablespace
	LEFT OUTER JOIN indexes AS i
		ON n.nspname = i.schemaname
		AND c.relname = i.tablename
WHERE c.relkind = 'm'::"char"
;

COMMENT ON VIEW pg_table_matview_infos IS 'The view shows detailed information about sizes and indexes of tables and materialized views';
COMMENT ON COLUMN pg_table_matview_infos.type IS 'The type of the result row, can be TABLE or MATERIALIZED VIEW.';
COMMENT ON COLUMN pg_table_matview_infos.schema_name IS 'The name of the schema where the object is stored.';
COMMENT ON COLUMN pg_table_matview_infos.object_name IS 'The name of the table or materialized view.';
COMMENT ON COLUMN pg_table_matview_infos.object_owner IS 'The role which is owning the obejct.';
COMMENT ON COLUMN pg_table_matview_infos.tablespace IS 'The table space where the object is stored.';
COMMENT ON COLUMN pg_table_matview_infos.indexes IS 'All indexes that have been created on this object.';
COMMENT ON COLUMN pg_table_matview_infos.object_size IS 'The size of the data of this object.';
COMMENT ON COLUMN pg_table_matview_infos.indexes_size IS 'The size of the indexes of this object.';
COMMENT ON COLUMN pg_table_matview_infos.total_object_size IS 'The overall size of this object.';
COMMENT ON COLUMN pg_table_matview_infos.total_object_size_pretty IS 'The size of the data of this object with unit.';
COMMENT ON COLUMN pg_table_matview_infos.indexes_size_pretty IS 'The size of the indexes of this object with unit.';
COMMENT ON COLUMN pg_table_matview_infos.total_relation_size_pretty IS 'The overall size of this object with the unit.';