Backlog Courrier

pour info :

-- launch
select * from  update_listinstance_delegate();			

-- function					
CREATE TYPE emp as 
(
	user_migrated int,
	detected_line int
);

create or replace function update_listinstance_delegate() returns emp AS $BODY$
DECLARE 
	rec record;
	id_user int;
	i int;
	a int;
	nb_user int;
	contenu varchar;
	nom varchar;
	prenom varchar;
begin
	a = 0;
	i = 0;
	create temp table x as select listinstance_id,btrim(split_part(ltrim(process_comment,'Action de:'),'--',1),' ')as col from listinstance where process_comment ilike 'Action de%';
	update x set col = btrim(substr(col,14),' ') where col ilike '%Action de%';
	FOR rec in SELECT listinstance_id,col from x
    loop
    	a = a + 1;
    	select split_part(rec.col,' ',2), split_part(rec.col,' ',1) into nom,prenom from x;
    	if nom = '' or prenom = '' then  
    		raise INFO 'LAKE OF INFORMATION FOR listinstance_id : %',rec.listinstance_id ;
    		continue;
    	end if;
    	select count(user_id) into nb_user from users where lastname = nom and firstname = prenom;
    	if nb_user > 1 then  
    		raise DEBUG 'USER % % WAS DOUBLE CHECKED',nom, prenom using HINT = '--> MANUAL ACTION NEEDED FOR listinstance_id : %' || rec.listinstance_id;
    		continue;
    	end if;
    	select user_id into id_user from users where lastname = nom and firstname = prenom;
    	if id_user isnull then  
    		raise WARNING 'USER % % NOT FOUND listinstance_id : %',nom, prenom,rec.listinstance_id;
    		continue;
    	end if;
    	--raise notice 'user id : %' , id_user;
    	i = i + 1;
    	update listinstance set delegate = id_user where listinstance_id = rec.listinstance_id;
    END LOOP;
   	return (i,a);
END;
$BODY$ LANGUAGE plpgsql;

-- reset 
update listinstance set delegate = null;

-- A lancer après utilisation
drop function update_listinstance_delegate;
drop type emp;
drop table x;