/**
 * The view tables with sequential scans, which probably are missing indexes
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

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


 	IF version_greater_15 THEN
 		-- Create the view pg_functions for PostgreSQL 15 or newer
		CREATE OR REPLACE VIEW pg_missing_indexes AS
		SELECT t.schemaname AS schema_name
			, t.relname AS table_name
			, t.seq_scan AS sequential_scans
			, t.last_seq_scan AS last_sequential_scans
		FROM pg_stat_all_tables AS t
		WHERE schemaname NOT IN
			(
				'pg_toast',
				'pg_catalog',
				'information_schema'
			)
		AND t.seq_scan > 0
		;

		COMMENT ON VIEW pg_missing_indexes IS 'The view tables with sequential scans, which probably are missing indexes. The higher the number the more likely an index is might be needed.';
		COMMENT ON COLUMN pg_missing_indexes.schema_name IS 'The name of the schema.';
		COMMENT ON COLUMN pg_missing_indexes.table_name IS 'The name of the table.';
		COMMENT ON COLUMN pg_missing_indexes.sequential_scans IS 'Number of sequential scans on this table.';
		COMMENT ON COLUMN pg_missing_indexes.last_sequential_scans IS 'Last time of a sequential scan on this table.';
	ELSE
		-- Create the view pg_functions for PostgreSQL 15 or newer
		CREATE OR REPLACE VIEW pg_missing_indexes AS
		SELECT t.schemaname AS schema_name
			, t.relname AS table_name
			, t.seq_scan AS sequential_scans
		FROM pg_stat_all_tables AS t
		WHERE schemaname NOT IN
			(
				'pg_toast',
				'pg_catalog',
				'information_schema'
			)
		AND t.seq_scan > 0
		;

		COMMENT ON VIEW pg_missing_indexes IS 'The view tables with sequential scans, which probably are missing indexes. The higher the number the more likely an index is might be needed.';
		COMMENT ON COLUMN pg_missing_indexes.schema_name IS 'The name of the schema.';
		COMMENT ON COLUMN pg_missing_indexes.table_name IS 'The name of the table.';
		COMMENT ON COLUMN pg_missing_indexes.sequential_scans IS 'Number of sequential scans on this table.';
	END IF;
END $$;