/**
 * Returns a table contianing the inheritance path of a given rolename.
 *
 * @author: "Stefanie Janine Stölting"<stefanie@proopensource.eu>
 * @license: PostgreSQL https://opensource.org/licenses/postgresql
 */

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

 	IF version_greater_15 THEN
 		-- Create the function for PostgreSQL 16 or newer
		function_source :=
$string$
CREATE OR REPLACE FUNCTION role_inheritance (role_name text)
	RETURNS table("oid" oid, rolename text, steps integer, role_path text, inherit_option boolean, set_option boolean)
AS $function$
BEGIN
	RETURN QUERY
	WITH RECURSIVE rcte AS
		(
			SELECT pr.oid AS "oid"
				, 0 AS steps
				, pr.oid::regrole::text AS path
				, pr.rolinherit AS inherit_option
				, NULL::boolean AS set_option
			FROM   pg_roles AS pr
			WHERE  rolname = role_name
			UNION ALL
			SELECT m.roleid
				, rcte.steps + 1
				, rcte.path || '->' || m.roleid::regrole::text
				, rcte.inherit_option AND m.inherit_option
				, m.set_option
			FROM rcte
			INNER JOIN pg_auth_members AS m
				ON rcte."oid" = m.member
	   )
	SELECT rcte."oid"
		, rcte."oid"::regrole::text AS rolename
		, rcte.steps
		, rcte.path AS role_path
		, rcte.inherit_option
		, rcte.set_option
	FROM rcte
	;
END;
$function$
LANGUAGE plpgsql
;
$string$
;
	ELSE
		-- Create the function for PostgreSQL older than 16
		function_source :=
$string$
CREATE OR REPLACE FUNCTION role_inheritance (role_name text)
	RETURNS table("oid" oid, rolename text, steps integer, role_path text)
AS $function$
BEGIN
	RETURN QUERY
	WITH RECURSIVE rcte AS
		(
			SELECT pr.oid AS "oid"
				, 0 AS steps
				, pr.oid::regrole::text AS path
			FROM   pg_roles AS pr
			WHERE  rolname = role_name
			UNION ALL
			SELECT m.roleid
				, rcte.steps + 1
				, rcte.path || '->' || m.roleid::regrole::text
			FROM rcte
			INNER JOIN pg_auth_members AS m
				ON rcte."oid" = m.member
	   )
	SELECT rcte."oid"
		, rcte."oid"::regrole::text AS rolename
		, rcte.steps
		, rcte.path AS role_path
	FROM rcte
	;
END;
$function$
LANGUAGE plpgsql
;
$string$
;
	END IF;

	EXECUTE function_source;

	COMMENT ON FUNCTION role_inheritance(s text) IS 'Returns a table contianing the inheritance path of a given rolename.';
ENd
$$;