check if group-member allocation already exist

Question

I have a schema that maps members to a group:

create table member (member_id serial, name varchar(10), primary key (member_id));
                    
create table groups (group_id serial, name varchar (10), primary key (group_id));

create table member_groups( 
  group_id integer, 
  member_id integer, 
  constraint fk_group foreign key (group_id) references groups(group_id), 
  constraint fk_member foreign key (member_id) references member(member_id)
);

How can I check if a group-member allocation already exists? e.g.:

insert into member (name) values('member 1'), ('member 2'), ('member 3'), ('member 4'), ('member 5');

insert into groups(name) values ('group 1'), ('group 2'), ('group 3');


insert into member_groups values (1, 1), (1,2), (1,4), (2,1), (2, 4);

another group with the same members, e.g. a group with members 1,2,4 like group 1 should not be allowed, a group with the members 1,2,3,4 should be allowed though. Probably this have to be checked by the application and could be not contrainted by the DDL (?).

DB-Fiddle

Answer 1

You can create a stored function set_members(group_id integer, members integer[]) to pass all the members in array and then do the validation inside the function. To validate you may use contains and contained by operators:

create function set_members(prm_group_id integer, prm_members integer[]) returns void
LANGUAGE plpgsql
SECURITY DEFINER
AS $fnk$
DECLARE
  _group integer;
BEGIN
  SELECT
    group_id
  INTO
    _group
  FROM 
    member_groups
  GROUP BY
    group_id
  HAVING 
    array_agg(member_id) @> prm_members
    AND array_agg(member_id) <@ prm_members
  LIMIT 1;
  
  IF FOUND THEN
    RAISE EXCEPTION 'Group with same members exists (id=%)', _group; 
  END IF;
  
  --insert members that are not included
  
  --your code here
  
  --delete no longer included members 
  
  --your code here
END
$fnk$;

To prevent inserting into member_groups directly you can use permissions allowing only the owner to insert and setting SECURITY DEFINER on the set_members function. That way it will be executed using permissions of the owner of the function.