简单介绍:
我有一个包含用户和组的数据库。
每个用户可能是一个或多个组的成员。
每个组可能有一个或多个父组。
架构:
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