简单介绍:
我有一个包含用户和组的数据库。
每个用户可能是一个或多个组的成员。
每个组可能有一个或多个父组。
架构:

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函数,该函数查找特定用户所属的所有组(其名称)。
例子:
组名:人,
组父级:空
组名:学生,
群父:人
组名:足球队员,
群父:人
组名:篮球队员,
群父:人
用户名:Maciej,
小组:学生、足球运动员
f(“Maciej”)={“学生”,“人”,“足球运动员”}
他属于“人”,就因为他属于“学生”或“足球运动员”。他不是“人民”组织的直接成员。
提前谢谢!

最佳答案

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

08-28 03:19