/**
 * Role infos based on pg_roles and role_inheritance.
 * NOTE: The function role_inheritance must exist!
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

DO $$
DECLARE
	function_exists BOOLEAN;
 	version_greater_15 BOOLEAN;
	view_source TEXT;
BEGIN

	SELECT count(*) = 1 AS function_exists
	INTO function_exists
	FROM pg_catalog.pg_proc AS f
	WHERE f.proname = 'role_inheritance'
		AND f.pronargs = 1
	;

	IF function_exists THEN
		-- The view is only created when the function role_inheritance
	 	SELECT to_number((string_to_array(version(), ' '))[2], '999.99') >= 16
		INTO version_greater_15;
	
	 	IF version_greater_15 THEN
	 		-- Create the view for PostgreSQL 16 or newer
			view_source :=
$string$
CREATE OR REPLACE VIEW pg_role_infos AS
SELECT pr.oid
	, pr.rolname AS rolename
	, pr.rolsuper
	, pr.rolcreaterole
	, pr.rolcreatedb
	, pr.rolreplication
	, pr.rolcanlogin
	, pr.rolvaliduntil
	, pr.rolconfig
	, ri.steps
	, ri.role_path
	, ri.inherit_option
FROM pg_catalog.pg_roles AS pr
, role_inheritance(pr.rolname) AS ri
;
$string$
;
			EXECUTE view_source;

			COMMENT ON COLUMN pg_role_infos.inherit_option IS 'True if the member can SET ROLE to the granted role.';
		ELSE
			-- Create the view for PostgreSQL older than 16
			view_source :=
$string$
CREATE OR REPLACE VIEW pg_role_infos AS
SELECT pr.oid
	, pr.rolname AS rolename
	, pr.rolsuper
	, pr.rolcreaterole
	, pr.rolcreatedb
	, pr.rolreplication
	, pr.rolcanlogin
	, pr.rolvaliduntil
	, pr.rolconfig
	, ri.steps
	, ri.role_path
FROM pg_catalog.pg_roles AS pr
, role_inheritance(pr.rolname) AS ri
;
$string$
;
			EXECUTE view_source;
		END IF;

		COMMENT ON VIEW pg_role_infos IS 'Role infos based on pg_roles and role_inheritance.';
		COMMENT ON COLUMN pg_role_infos.oid IS 'The object identifier of the role.';
		COMMENT ON COLUMN pg_role_infos.rolename IS 'The name of the role.';
		COMMENT ON COLUMN pg_role_infos.rolsuper IS 'The role has superuser privileges.';
		COMMENT ON COLUMN pg_role_infos.rolcreaterole IS 'The role can create more roles.';
		COMMENT ON COLUMN pg_role_infos.rolcreatedb IS 'The role can create databases.';
		COMMENT ON COLUMN pg_role_infos.rolreplication IS 'The role is a replication role. A replication role can initiate replication connections and create and drop replication slots.';
		COMMENT ON COLUMN pg_role_infos.rolcanlogin IS 'The role can log in. Usually group roles cannot login.';
		COMMENT ON COLUMN pg_role_infos.rolvaliduntil IS 'The password expiry time, NULL if no expiration.';
		COMMENT ON COLUMN pg_role_infos.rolconfig IS 'The role specific defaults for run-time configuration variables, for example the search path.';
		COMMENT ON COLUMN pg_role_infos.steps IS 'The order im which the inheritance is going.';
		COMMENT ON COLUMN pg_role_infos.role_path IS 'The path of the inheritance.';

	END IF;

END $$;