Gym5iwk2

1create or replace view cases as
2  select  a.case_id as case_id
3         ,b.displayName as displayName
4         ,b.clientName  as clientName
5         ,b.batesPreTxt as batesPreTxt
6         ,a.memberRole  as memberRole
7         ,a.memberEmail as memberEmail
8         ,a.secrecy     as secrecy
9         ,b.hasProtectiveOrder as hasProtectiveOrder
10         ,b.active      as active
11  from case_roles as a
12  join raw_cases as b using (case_id)
13  where a.memberEmail = basic_auth.current_email() and a.memberRole > 'unassigned';
14
15grant select, insert, update on table cases to contributor, automation, admin;
16
17drop trigger if exists update_cases on cases;
18create or replace function update_cases() returns trigger
19language plpgsql
20AS $$
21declare
22  case_entry cases%ROWTYPE;
23begin
24  if tg_op = 'INSERT' then
25    set role automation;
26    insert into raw_cases (displayname, clientname, batespretxt, hasprotectiveorder)
27      values (new.displayname, new.clientname, new.batespretxt, new.hasprotectiveorder);
28    insert into case_roles (case_id, memberEmail, memberRole)
29      values (lastval(), basic_auth.current_email(), 'progenitor');
30    select * into case_entry from cases where case_id = lastval();
31    reset role;
32    return case_entry;
33  elsif tg_op = 'UPDATE' then
34    if old.case_id != new.case_id then
35      -- It's not okay to swap out the case_id
36      return null;
37    end if;
38    set role automation;
39      if old.clientname != new.clientname then
40        update raw_cases set clientname = new.clientname where case_id = new.case_id;
41      end if;
42      if old.displayname != new.displayname then
43        update raw_cases set displayname = new.displayname where case_id = new.case_id;
44      end if;
45      if old.batespretxt != new.batespretxt then
46        update raw_cases set batespretxt = new.batespretxt where case_id = new.case_id;
47      end if;
48      if old.hasprotectiveorder != new.hasprotectiveorder then
49        update raw_cases set hasprotectiveorder = new.hasprotectiveorder where case_id = new.case_id;
50      end if;
51      if old.active != new.active then
52        update raw_cases set active = new.active where case_id = new.case_id;
53      end if;
54
55      select * into case_entry from cases where case_id = new.case_id;
56    reset role;
57    return case_entry;
58  elsif tg_op = 'DELETE' then
59    set role automation;
60    delete from case_roles where case_id = old.case_id;
61    delete from cases where case_id = old.case_id;
62    reset role;
63    return null;
64  end if;
65end
66$$;
67
68create trigger update_cases 
69  instead of insert or update or delete on
70    cases for each row execute procedure update_cases();