Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


-- setup 
create table users( usr_id integer generated always as identity
                                   primary key
                  , name   text    not null unique  
                  ) ; 
insert into users(name) 
       values ('user-a'), ('user-b'); 
       
select * from users; 

create table projects( proj_id integer generated always as identity
                                       primary key
                     , name    text    not null unique 
                     );
                    
insert into projects(name) 
       values ('project 1'), ('project 2'); 
                     
select * from projects;  


  
create table project_assignments( proj_id    integer 
                                             references projects(proj_id)
                                , usr_id     integer
                                             references users(usr_id)
                                , created_at timestamptz not null default current_timestamp
                                , updated_at timestamptz   
                                , constraint project_assignments_pk
                                             primary key(proj_id, usr_id)
                               ); 
                              
select * from project_assignments; 

CREATE TABLE



INSERT 0 2




usr_id
name


1
user-a

2
user-b



SELECT 2



CREATE TABLE



INSERT 0 2




proj_id
name


1
project 1

2
project 2



SELECT 2



CREATE TABLE




proj_id
usr_id
created_at
updated_at



SELECT 0


-- standard Insert, Update functions for  project assignments                             
create or replace procedure assign_project_to_user(in project_name text, in user_name text)
 language sql
as $$
    insert into project_assignments(proj_id, usr_id) 
        select * from 
           ( select proj_id from projects where name = project_name ) s1
         , ( select usr_id  from users    where name = user_name) s2 ; 
$$;
                              
create or replace procedure reassign_project_to_user(in project_name text, in new_user_name text)
 language sql
as $$
    update project_assignments 
       set usr_id = (select usr_id 
                       from users 
                      where name = new_user_name
                    )
         , updated_at = current_timestamp
     where proj_id = (select proj_id 
                        from projects
                        where name = project_name
                     );
$$;  

CREATE PROCEDURE



CREATE PROCEDURE


--- Create View for Project User assignments    
create view assignments as 
       select proj.name  "Project Name" 
            , agn.name   "Assigned User"
            , agn.created_at
            , agn.updated_at
         from projects  proj
         left join ( select pa.proj_id
                          , usr.name 
                          , pa.created_at
                          , pa.updated_at
                       from project_assignments pa
                       join users usr   
                         on (usr.usr_id = pa.usr_id)
                   ) agn 
                 on (agn.proj_id = proj.proj_id) ; 

CREATE VIEW


-- Trigger Function for DML operations on VIEW
create or replace function  make_project_assignment_iot()
 returns trigger
 language plpgsql
as $$
begin  
    case TG_OP
	 when 'INSERT' then 
	      call assign_project_to_user( new."Project Name", new."Assigned User");
	      return new; 
	      
	 when 'UPDATE' then 
	      call reassign_project_to_user( new."Project Name", new."Assigned User");
	      return new; 
	      
	 when 'DELETE' then
	      raise exception 'Delete from view ASSIGNMENTS not yet developed'; 
    end case;
end;  
$$;

CREATE FUNCTION


-- Trigger  for DML on view
create or replace trigger project_assignments_iot
  instead of delete or insert or update  
       on assignments
      for each row
  execute function make_project_assignment_iot();   

CREATE TRIGGER


select * 
  from assignments 
 order by "Project Name" ;


Project Name
Assigned User
created_at
updated_at


project 1
null
null
null

project 2
null
null
null



SELECT 2


-- insert into VIEW
insert into assignments("Project Name" , "Assigned User")
     values ('project 2', 'user-b'  ); 
    
select * 
  from assignments 
 order by "Project Name" ;

INSERT 0 1




Project Name
Assigned User
created_at
updated_at


project 1
null
null
null

project 2
user-b
2024-05-21 03:07:49.361826+01
null



SELECT 2


update assignments 
   set "Assigned User" = 'user-a'
 where "Assigned User" = 'user-b';

select * 
  from assignments 
 order by "Project Name" 

UPDATE 1




Project Name
Assigned User
created_at
updated_at


project 1
null
null
null

project 2
user-a
2024-05-21 03:07:49.361826+01
2024-05-21 03:07:49.365376+01



SELECT 2


delete 
  from assignments 
 where "Project Name" = 'project 2';

ERROR:  Delete from view ASSIGNMENTS not yet developed
CONTEXT:  PL/pgSQL function make_project_assignment_iot() line 13 at RAISE
Project Name	Assigned User	created_at	updated_at
project 1	null	null	null
project 2	user-b	2024-05-21 03:07:49.361826+01	null