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$

;

PostgreSQL: recursive query

Suppose you have the following sql tables:
Table "group"
id: int8
name: varchar

Table "group_member"
id: int8
group_id: int8
member_user_id: int8
member_group_id: int8


The column member_group_id is filled when you have a group
that is part of the group identified with group_id.

Suppose you want to determine all groups that are part of a 
specific group. You need a recursive query for that:

-- Met deze query willen we alle groepen bepalen die in onze
-- groep zitten.
-- Als een groep lid is van onze groep, dan zijn dat ook 
-- users die we moeten ophalen.
-- we moeten dus de huidige groep en de groepen die lid 
-- zijn van onze groep bepalen. En dat recursief.
with recursive groups AS (
  select
    g.id as group_id,
    gm.member_group_id as member_group_id
  from "group" g
  -- welke groepen zijn members van de huidige groep?
  left outer join group_member gm on gm.group_id = g.id and gm.member_group_id is not null
  where g.id = specific_group_id
  --  
  union
  --
  select
    g.id as group_id,
    gm.member_group_id as member_group_id
  from "group" g
  -- welke groepen zijn members van de huidige groep?
  left outer join group_member gm on gm.group_id = g.id and gm.member_group_id is not null
  -- we doen de query vanuit de member_group_id van het resultaat van de recursie
  inner join groups rg on rg.member_group_id = g.id
)
select distinct(group_id) from groups;

AxonServer: Table "token_entry" not found

When using AxonServer and you include a @EventHandler in your code, the AxonServer told me that the table "token_entry" was not available.

The problem was that I used Spring-JPA-NHibernate in my code. I defined an entitymanager for JPA and that messed up the entitymanager for Axon. After removing the JPA code and replacing it with jetbrains Exposed, the problem was gone.

An addition setting that I activated was in the application.properties:

spring.jpa.hibernate.ddl-auto=create-drop

You can download the AxonServer at this url: https://download.axoniq.io/axonserver/AxonServer.zip