I've two tables the first contains information about the patients, and the second contains information about doctors. To create access control, I've create theses views:
View 1
CREATE OR REPLACE VIEW public.two_tables AS
SELECT relation1.calling_time,
relation1.pk_arriving_time,
relation1.name,
relation1.pk_address,
relation1.diagnos,
relation1.hospitalisation,
relation1.actions,
relation1.dispatcher,
relation1.ambulance_number,
relation2.medic,
relation2.post,
relation2.pk_ambulance_number
FROM relation1
LEFT JOIN relation2 ON relation1.ambulance_number = relation2.pk_ambulance_number;
View 2
CREATE OR REPLACE VIEW public.doctor_view AS
SELECT
CASE
WHEN two_tables.medic = CURRENT_USER THEN two_tables.pk_address
ELSE '*****'::text
END AS pk_address,
CASE
WHEN two_tables.medic = CURRENT_USER THEN two_tables.name
ELSE '*****'::text
END AS name,
CASE
WHEN two_tables.medic = CURRENT_USER THEN two_tables.diagnos
ELSE '*****'::text
END AS diagnos,
CASE
WHEN two_tables.medic = CURRENT_USER THEN two_tables.actions
ELSE '*****'::text
END AS actions,
two_tables.calling_time,
two_tables.pk_arriving_time,
two_tables.hospitalisation,
two_tables.dispatcher,
two_tables.ambulance_number,
two_tables.medic,
two_tables.post
FROM two_tables;
So in that way doctors can only see private information only about their own patients, and public info about all patients. Now I'm trying to realize UPDATE command, like that: update doctor_view set hospitalisation ='some hos.', actions = 'some act.' where pk_arriving_time = 'some time' and pk_address = 'some add'. So my question is, is there any way to make view updatable. I was trying to create trigger or rules, but I don't know how to make them update view "doctor_view" properly.
INSTEAD OF UPDATE
trigger on the view.