0

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.

5
  • You need an INSTEAD OF UPDATE trigger on the view. May 8 at 11:27
  • As I know INSTEAD OF UPDATE trigger can replace only special update, I mean for every queries like: update doctor_view set hospitalisation ='some hos.', actions = 'some act.' where pk_arriving_time = 'some time' , or update doctor_view set diagnos ='some dig.' where actions= 'some time' and hospitalisation = 'some hosp.' I will need new trigger. Tell me, please, how right am I?
    – Ilya
    May 8 at 12:41
  • No; if you do it right, a single trigger can handle all your updates. May 8 at 14:33
  • Thanks for your answer, can you give me a little example for my case above with two queries, or some documentation for this case so I could read it by myself?
    – Ilya
    May 8 at 18:35
  • Too lazy. Basically, the trigger function performs the appropriate updates on the constituent tables. If someone tries to update the NULLs that were generated by the outer join, you can consider throwing an error or inserting a row. May 8 at 20:14

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.