PostgreSQL: Making a stored procedure for retrieving groups

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$

;