Подзапрос, возвращающий несколько столбцов - или близкое приближение
У меня интересная проблема, однако я не знаю, как лучше ее сформулировать, чем сказать, что у меня есть подзапрос, который должен возвращать несколько столбцов. PostgreSQL выдает ошибку, когда я пытаюсь это сделать, поэтому, хотя мой SQL кажется мне логически разумным, очевидно, что есть лучший способ сделать это. Я' m пытается объединить разрешения пользователей в одну таблицу (в надежде добавить это в представление или даже в своего рода «материализованное представление»). Вот мои таблицы:
CREATE TABLE users (
user_id integer NOT NULL,
username character varying(32) NOT NULL,
passwd character varying(32) NOT NULL,
dept_id integer NOT NULL,
last_activity timestamp with time zone NOT NULL DEFAULT now(),
CONSTRAINT "pk-users-user_id" PRIMARY KEY (user_id)
);
CREATE TABLE groups (
group_id integer NOT NULL,
group_name character varying(32) NOT NULL,
add_posts integer NOT NULL DEFAULT 0,
remove_posts integer NOT NULL DEFAULT 0,
modify_users integer NOT NULL DEFAULT 0,
add_users integer NOT NULL DEFAULT 0,
delete_users integer NOT NULL DEFAULT 0,
CONSTRAINT "pk-groups-group_id" PRIMARY KEY (group_id)
);
CREATE TABLE user_groups (
user_id integer NOT NULL,
group_id integer NOT NULL,
CONSTRAINT "fk-user_groups-group_id" FOREIGN KEY (group_id)
REFERENCES groups (group_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "fk-user_groups-user_id" FOREIGN KEY (user_id)
REFERENCES users (user_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);
CREATE TABLE user_rights (
user_id integer NOT NULL,
add_posts integer NOT NULL DEFAULT 0,
remove_posts integer NOT NULL DEFAULT 0,
modify_users integer NOT NULL DEFAULT 0,
add_users integer NOT NULL DEFAULT 0,
delete_users integer NOT NULL DEFAULT 0,
CONSTRAINT "fk-user_rights-user_id" FOREIGN KEY (user_id)
REFERENCES users (user_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
);
и некоторые данные для их заполнения:
INSERT INTO users(user_id, username, passwd, dept_id) VALUES (1, 'nicole','123456',12);
INSERT INTO users(user_id, username, passwd, dept_id) VALUES (2, 'john','324634',11);
INSERT INTO users(user_id, username, passwd, dept_id) VALUES (3, 'susan','61236',14);
INSERT INTO users(user_id, username, passwd, dept_id) VALUES (4, 'mary','1213612',2);
INSERT INTO user_rights(user_id, add_posts, remove_posts, modify_users, add_users, delete_users) VALUES (1,0,0,1,1,1);
INSERT INTO user_rights(user_id, add_posts, remove_posts, modify_users, add_users, delete_users) VALUES (2,1,1,1,1,1);
INSERT INTO user_rights(user_id, add_posts, remove_posts, modify_users, add_users, delete_users) VALUES (3,0,0,0,0,0);
INSERT INTO user_rights(user_id, add_posts, remove_posts, modify_users, add_users, delete_users) VALUES (4,0,0,0,0,0);
INSERT INTO groups(group_id, group_name, add_posts, remove_posts, modify_users, add_users, delete_users) VALUES (1,'Poster',1,1,0,0,0);
INSERT INTO groups(group_id, group_name, add_posts, remove_posts, modify_users, add_users, delete_users) VALUES (2,'User Mgr',0,0,1,1,1);
INSERT INTO groups(group_id, group_name, add_posts, remove_posts, modify_users, add_users, delete_users) VALUES (3,'Admin',1,1,1,1,1);
INSERT INTO user_groups(user_id, group_id) VALUES (1,1);
INSERT INTO user_groups(user_id, group_id) VALUES (2,2);
INSERT INTO user_groups(user_id, group_id) VALUES (3,2);
INSERT INTO user_groups(user_id, group_id) VALUES (4,3);
INSERT INTO user_groups(user_id, group_id) VALUES (1,2);
Я пытаюсь создать запрос, который может вычислить эффективные разрешения, которые может иметь пользователь. Пользователи хранятся в (как вы уже догадались) таблице «пользователи». Группы в «группах», какие бы группы ни были назначены пользователю, находятся в «user_groups». Наконец, каждый пользователь может иметь индивидуальные разрешения, которые должны переопределять разрешения группы - они хранятся в 'user_rights'.
Я могу получить запрос всей этой информации, используя (и да, я знаю, что это некрасиво):
select
max(add_posts) as add_posts,
max(remove_posts) as remove_posts,
max(modify_users) as modify_users,
max(add_users) as add_users,
max(delete_users) as delete_users
from
(
select
max(add_posts) as add_posts,
max(remove_posts) as remove_posts,
max(modify_users) as modify_users,
max(add_users) as add_users,
max(delete_users) as delete_users
from
groups
where
group_id in (select group_id from user_groups where user_id = 3)
union all
select
max(add_posts) as add_posts,
max(remove_posts) as remove_posts,
max(modify_users) as modify_users,
max(add_users) as add_users,
max(delete_users) as delete_users
from
user_rights
where
user_id = 3
) as combined_user_groups
Что, учитывая приведенные выше данные, даст мне эффективные разрешения для любого пользователя, указанного в предложениях WHERE. Я хочу создать материализованное представление, которое обновляется только при изменении данных пользователя или группы, но в остальном остается статичным. Я знаю, как это сделать без проблем - проблема, с которой я сталкиваюсь, создает это представление. Моя идея заключается в использовании вышеуказанного запроса, но с его запуском для каждого пользователя в таблице 'users' и созданием столбца 'user_id'. Итак, моя таблица «эффективных_постановлений» будет выглядеть так:
user_id, add_posts, remove_posts, modify_users, add_users, delete_users
1 1 1 1 1 1
2 1 1 1 1 1
3 0 0 1 1 1
.. и так далее. Я просто не могу понять, как добавить user_id к этому результату и показать несколько строк. Надеюсь, я предоставил достаточно информации, чтобы кто-то понял, что я пытаюсь сделать. Я понимаю, что в конечном итоге этот метод может стать довольно дорогостоящим с точки зрения производительности, если таблицы сгруппированы по размеру - и это решение, кажется, лучшее, что я могу придумать для смягчения этой проблемы.
Приведенные примеры должны работать, если вы хотите воссоздать образцы данных для целей тестирования (я просто быстро перестроил их на своем локальном сервере pg, хотя это намного проще, чем настоящие таблицы, применимы те же концепции).