After this, ofcourse you want to do something like:
select * from allgroups(1000);
Then you need a function that returns a resultset in PostgreSQL:
CREATE OR REPLACE FUNCTION public.allgroups(p_id int8)
RETURNS table (res_group_id int8)
LANGUAGE plpgsql
AS $function$
begin
return query
with recursive groups AS (
select
g.id as group_id,
gm.member_group_id as member_group_id
from "group" g
left outer join group_member gm on gm.group_id = g.id and gm.member_group_id is not null -- welke groepen zijn members van de huidige groep?
where g.id = p_id
--
union
--
select
g.id as group_id,
gm.member_group_id as member_group_id
from "group" g
left outer join group_member gm on gm.group_id = g.id and gm.member_group_id is not null -- welke groepen zijn members van de huidige groep?
inner join groups rg on rg.member_group_id = g.id -- we doen de query vanuit de member_group_id van het resultaat van de recursie
)
select distinct(group_id) from groups;
end;
$function$
;
And a count function:
CREATE OR REPLACE FUNCTION public.allgroupscount(p_id int8)
RETURNS int8
LANGUAGE plpgsql
AS $function$
begin
return (select count(*) from public.allgroups(p_id));
end;
$function$
;
