/**
 * Creates a view to get information about partitioned tables.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

DO $$
DECLARE
	version_greater_10 BOOLEAN;
BEGIN
	SELECT to_number((string_to_array(version(), ' '))[2], '999.99') >= 10 INTO version_greater_10;

	IF version_greater_10 THEN
		-- Create the view pg_functions for PostgreSQL 10 or newer
		CREATE OR REPLACE VIEW pg_partitioned_tables_infos AS
		SELECT cl.oid AS parent_relid
			, n.nspname AS parent_schemaname
			, cl.relname AS parent_tablename
			, r.rolname AS parent_owner
			, CASE pt.partstrat
				WHEN 'l' THEN
					'LIST'
				WHEN 'r' THEN
					'RANGE'
				WHEN 'h' THEN
					'HASH'
			END AS partition_strategy
			, count (cl2.oid) OVER (PARTITION BY cl.oid) AS count_of_partitions
			, COALESCE (sum (pg_relation_size (cl2.oid)) OVER (PARTITION BY cl.oid), 0) AS overall_size
			, cl2.oid AS child_relid
			, n2.nspname AS child_schemaname
			, cl2.relname AS child_tablename
			, r2.rolname AS child_owner
			, pg_relation_size (cl2.oid) AS child_size
		FROM pg_catalog.pg_class AS cl
		INNER JOIN pg_catalog.pg_partitioned_table AS pt
			ON cl.oid = pt.partrelid
		INNER JOIN pg_catalog.pg_namespace AS n
			ON cl.relnamespace = n.oid
		INNER JOIN pg_catalog.pg_roles AS r
			 ON cl.relowner = r.oid
		LEFT OUTER JOIN pg_catalog.pg_inherits AS i
			ON cl.oid = i.inhparent
		LEFT OUTER JOIN pg_catalog.pg_class AS cl2
			ON i.inhrelid = cl2.oid
			AND cl2.relispartition
			AND cl2.relkind = 'r'
		LEFT OUTER JOIN pg_catalog.pg_namespace AS n2
			ON cl2.relnamespace = n2.oid
		LEFT OUTER JOIN pg_catalog.pg_roles AS r2
			 ON cl2.relowner = r2.oid
		WHERE cl.relkind = 'p'
		;

		COMMENT ON VIEW pg_partitioned_tables_infos IS 'Creates a view to get information about partitioned tables';
		COMMENT ON COLUMN pg_partitioned_tables_infos.parent_relid IS 'The oid of the parent table.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.parent_schemaname IS 'The schema where the parent table is located.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.parent_tablename IS 'The name of the parent table.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.parent_owner IS 'The role name of the owner of the parent table.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.partition_strategy IS 'The partition strategy of the partitioned table, this can be LIST, RANGE, or HASH.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.count_of_partitions IS 'The number of partitions of the parent table.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.overall_size IS 'The overall size of the table including all partitions.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.child_relid IS 'The oid of the partition of a partitioned table.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.child_schemaname IS 'The name of the schema where the partition of a partitioned table is located.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.child_tablename IS 'The name of the partition of a partitioned table.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.child_owner IS 'The role name of the owner of the partition of a partitioned table.';
		COMMENT ON COLUMN pg_partitioned_tables_infos.child_size IS 'The size of this partition.';
	END IF;

END $$;