valor una tipos subconsultas subconsulta poner otro nombre mas doble devuelve dentro datos consulta con columna anidadas sql postgresql permissions usergroups

sql - una - subconsultas base de datos



Subconsulta que devuelve varias columnas, o una aproximación cercana (1)

select user_id 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 ug.user_id max(g.add_posts) as add_posts, max(g.remove_posts) as remove_posts, max(g.modify_users) as modify_users, max(g.add_users) as add_users, max(g.delete_users) as delete_users from groups g inner join users_groups ug on g.group_id = ug.group_id group by ug.user_id union select user_id 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 group by user_id ) as combined_user_groups group by user_id

Tengo un problema interesante, sin embargo, no sé cómo articularlo mejor que decir que tengo una subconsulta que debe devolver varias columnas. PostgreSQL arroja un error cuando intento hacerlo, por lo tanto, aunque mi SQL me parece algo lógicamente correcto, obviamente hay una mejor manera de hacerlo. Estoy intentando fusionar permisos de usuario en una tabla (con la esperanza de arrojar esto a una vista o incluso a una "vista materializada" de géneros). Aquí están mis tablas:

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 );

y algunos datos para poblarlos:

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);

Lo que intento hacer es crear una consulta que pueda calcular los permisos efectivos que un usuario puede tener. Los usuarios se almacenan en la (''adivinada'') tabla de ''usuarios''. Los grupos en ''grupos'', cualquiera que sea el grupo al que se le pueda asignar un usuario, están en ''grupos de usuarios''. Finalmente, cada usuario puede tener permisos individuales que deben anular los permisos del grupo, que se almacenan en ''user_rights''.

Puedo hacer una consulta de toda esta información usando (y sí, sé que esto es feo):

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

Lo cual, dados los datos anteriores, me dará los permisos efectivos para cualquier usuario que especifique en las cláusulas WHERE. Lo que quiero hacer es crear una vista materializada que solo se actualice cuando los datos del usuario o del grupo cambian, pero que de lo contrario es estática. Esto sé cómo hacerlo sin ningún problema, el problema que encuentro es generar esta vista. Mi idea es usar la consulta anterior, pero hacer que se ejecute para cada usuario en la tabla de ''usuarios'' y crear una columna ''user_id''. Entonces mi tabla de ''effective_permissions'' se vería así:

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

..y así. Simplemente no puedo entender cómo agregar user_id a este resultado y mostrar varias filas. Espero haber proporcionado información suficiente para que alguien entienda qué es lo que trato de hacer. Me doy cuenta de que, en última instancia, este método puede llegar a ser bastante costoso en cuanto a rendimiento una vez que las tablas se agrupan en tamaño, y esta solución parece ser la mejor que se me ocurre para mitigar ese problema.

Los ejemplos proporcionados deberían funcionar si desea volver a crear los datos de muestra para fines de prueba (simplemente lo reconstruí en mi servidor de pg local muy rápido, aunque es mucho más simple que en las tablas reales se aplican los mismos conceptos).