Concrete example: leihs legacy knows about roles for users. We would like to extend this to include roles for groups. But we can not change code of leihs legacy. Solution: we add new functionality but make leihs legacy belive the DB schema has not changed.
- Add new tables for new functionality, i.e.
group_roles
- Rename existing table, i.e.
user_roles
todirect_user_roles
. - Create a view
user_roles
with customAGGREGATE
s usingdirect_user_roles
andgroup_roles
. - Optinally: make the views, i.e. `user_roles`` writable with triggers.
https://www.postgresql.org/docs/current/sql-createaggregate.html
CREATE OR REPLACE FUNCTION role_agg_f (role1 text, role2 text)
RETURNS text AS $$
BEGIN
IF role1 = 'inventory_manager' OR role2 = 'inventory_manager' THEN
RETURN 'inventory_manager';
ELSIF role1 = 'lending_manager' OR role2 = 'lending_manager' THEN
RETURN 'lending_manager';
ELSIF role1 = 'group_manager' OR role2 = 'group_manager' THEN
RETURN 'group_manager';
ELSIF role1 = 'customer' OR role2 = 'customer' THEN
RETURN 'customer';
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE AGGREGATE role_agg (text)
( sfunc = role_agg_f,
stype = text
);
CREATE VIEW access_rights AS
SELECT
ar_uuid_agg(id, user_id, inventory_pool_id) AS id,
origin_table_agg(origin_table) AS origin_table,
inventory_pool_id,
user_id,
role_agg(role) AS role
FROM unified_access_rights
GROUP BY (inventory_pool_id, user_id);
https://www.postgresql.org/docs/current/sql-createtrigger.html
-- INSERT on view access_rights view ------------------------------------------
CREATE OR REPLACE FUNCTION access_rights_on_insert_f()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.id iS NULL then
NEW.id = uuid_generate_v4();
END IF;
INSERT INTO direct_access_rights(id, user_id, inventory_pool_id, role)
VALUES (NEW.id, NEW.user_id, NEW.inventory_pool_id, NEW.role);
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER access_rights_on_insert_t
INSTEAD OF insert ON access_rights
FOR EACH ROW EXECUTE PROCEDURE access_rights_on_insert_f();