sql - PL/pgSQL : finding all groups person belongs to (also indirectly)

Question

简单介绍:

我有一个包含用户和组的数据库。每个用户都可能是一个或多个组的成员。每个组可能有一个或多个父组。

架构:

CREATE TABLE users(
  username VARCHAR(64) NOT NULL PRIMARY KEY,
  password VARCHAR(64) NOT NULL,
  enabled BOOLEAN NOT NULL);

CREATE TABLE groups (
  id bigserial NOT NULL PRIMARY KEY,
  group_name VARCHAR(64) NOT NULL);

CREATE TABLE groups_inheritance (
  group_id bigint NOT NULL,
  parent_group_id bigint NOT NULL,
  CONSTRAINT fk_group_inheritance_group FOREIGN KEY(group_id) REFERENCES groups(id),
  CONSTRAINT fk_group_inheritance_group_2 FOREIGN KEY(parent_group_id) REFERENCES groups(id),
  CONSTRAINT unique_uk_groups_inheritance UNIQUE(group_id, parent_group_id));

CREATE TABLE group_members (
  id bigint PRIMARY KEY,
  username VARCHAR(64) NOT NULL,
  group_id bigint NOT NULL,
  CONSTRAINT fk_group_members_username FOREIGN KEY(username) REFERENCES users(username),
  CONSTRAINT fk_group_members_group FOREIGN KEY(group_id) REFERENCES groups(id));

我正在寻找一个 PL/pgSQL 函数，它可以找到特定用户所属的所有组(他们的名字)。

例子:

组名:人，父组:空

组名:学生，父组:人

组名:Football_players,父组:人

组名:Basketball_players,父组:人

用户名:Maciej,组:学生，Football_players

f("Maciej") = {"Students", "People", "Football_players"

他属于“People”只是因为他属于“Students”或“Football_players”。他不是“People”组的直接成员。

提前致谢!

Answer 1

WITH RECURSIVE group_ancestry AS (
SELECT group_id, username
FROM group_members
UNION
SELECT groups_inheritance.parent_group_id, username
FROM group_ancestry
     JOIN groups_inheritance ON groups_inheritance.group_id = group_ancestry.group_id
)
SELECT username, group_id
FROM group_ancestry