
create table app_role( role_id int PRIMARY KEY,
						role_name varchar(32));

create table app_role_inherits( role_id int,
								parent_role_id int,
								UNIQUE ( role_id, parent_role_id ),
								FOREIGN KEY ( parent_role_id ) references app_role( role_id ) );

create table app_role_option( option_id int PRIMARY KEY, option_name varchar(32) );

create table app_role_option_map ( role_id int, option_id int,
								FOREIGN KEY ( role_id ) references app_role( role_id ),
								FOREIGN KEY ( option_id ) references app_role_option( option_id )
								);

create or replace function get_role_options( p_role_id int ) returns setof int as $$
declare
	rec record;
	rec2 record;
begin
	for rec in select option_id from app_role_option_map where role_id = p_role_id loop
		return next rec.option_id;
	end loop;
	
	for rec in select parent_role_id from app_role_inherits where role_id = p_role_id loop
		for rec2 in select s from get_role_options( rec.parent_role_id ) as s loop
			return next rec2.s;
		end loop;
	end loop;
end;
$$ language plpgsql;

-- role_1  -->  option_1 + option_2
-- role_2  -->  option_3 and inherits role_1
-- role_3  -->  option_2 + option_4
-- role_4  -->  option_5 and inherits role_2 and role_3

insert into app_role values( 1, 'role_1' );
insert into app_role values( 2, 'role_2' );
insert into app_role values( 3, 'role_3' );
insert into app_role values( 4, 'role_4' );

insert into app_role_option values( 1, 'option_1' );
insert into app_role_option values( 2, 'option_2' );
insert into app_role_option values( 3, 'option_3' );
insert into app_role_option values( 4, 'option_4' );
insert into app_role_option values( 5, 'option_5' );

insert into app_role_inherits values( 2, 1 );
insert into app_role_inherits values( 4, 2 );
insert into app_role_inherits values( 4, 3 );

insert into app_role_option_map values( 1, 1 );
insert into app_role_option_map values( 1, 2 );
insert into app_role_option_map values( 2, 3 );
insert into app_role_option_map values( 3, 2 );
insert into app_role_option_map values( 3, 4 );
insert into app_role_option_map values( 4, 5 );

select * from get_role_options( 4 );
