Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

DROP TABLE IF EXISTS resource_actions CASCADE;
CREATE TABLE resource_actions(
resource TEXT NOT NULL,
actions TEXT NOT NULL,
entity TEXT NOT NULL,
UNIQUE(resource, actions, entity)
);

INSERT INTO resource_actions (resource, actions, entity) VALUES
('reading-list', 'read', 'Citizen'),
('reading-list', 'read', 'Employee'),
('books', 'read', 'Employee'),
('titles', 'update', 'Citizen'),
('titles', 'read', 'Citizen'),
('titles', 'update', 'Employee'),
('titles', 'read', 'Employee'),
('authors', 'update', 'Employee'),
('authors', 'read', 'Citizen'),
('reviews', 'read', 'Citizen'),
('reviews', 'read', 'Employee'),
('reviews', 'create', 'Citizen'),
('reviews', 'create', 'Employee'),
('employees', 'read', 'Employee'),
('employees', 'read', 'Boss'),
('employees', 'delete', 'Boss'),
('employees', 'create', 'Boss'),
('employees', 'update', 'Boss'),
('employee-schedule', 'read', 'Boss'),
('employee-schedule', 'delete', 'Boss'),
('employee-schedule', 'create', 'Boss'),
('employee-schedule', 'update', 'Boss');

select json_build_object(actions, array_agg(groupedResources)) as jsonResult
from (select actions, json_build_object(entity, array_agg(resource)) as groupedResources
      from resource_actions
      group by actions, entity) tmp
group by actions;

DROP TABLE

CREATE TABLE

INSERT 0 22

jsonresult
{"create" : [{"Citizen" : ["reviews"]},{"Employee" : ["reviews"]},{"Boss" : ["employees","employee-schedule"]}]}
{"read" : [{"Boss" : ["employees","employee-schedule"]},{"Citizen" : ["reading-list","titles","authors","reviews"]},{"Employee" : ["reading-list","books","titles","reviews","employees"]}]}
{"update" : [{"Boss" : ["employees","employee-schedule"]},{"Citizen" : ["titles"]},{"Employee" : ["titles","authors"]}]}
{"delete" : [{"Boss" : ["employees","employee-schedule"]}]}

SELECT 4