-- ~ EX2
drop function if exists ex2(int);
create function ex2(id int)
returns int
as $$
	begin
	return sum(contrat.date_fin-contrat.date_debut)*categorie.prix_par_jour from contrat join voiture on contrat.id_voiture=voiture.id_voiture join categorie on voiture.id_categorie=categorie.id_categorie where categorie.id_categorie=id and id_etat=3 group by categorie.prix_par_jour;
	end;
$$ language plpgsql;


 -- ~ EX3
drop function if exists ex3(int);
create function ex3(id int)
returns text
as $$
	declare
		tmp int;
	begin
		select ex2(id) into tmp;
		if tmp=0 then
			return 'inexistant';
		end if;
		if tmp<500 then
			return 'faible';
		end if;
		if tmp<1000 then
			return 'moyen';
		end if;
		if tmp>=1000 then
			return 'bon';
		else
			return 'inexistant';
		end if;

	end;
$$ language plpgsql;


-- ~ EX4
drop function if exists ex4();
create function ex4()
returns SETOF RECORD
as $$
	select id_categorie, libelle_categorie, ex2(id_categorie), ex3(id_categorie)  from categorie;
$$ language sql;