CREATE FUNCTION livre_emprunt_tranche (integer)
 RETURNS int AS $$
 DECLARE
	nbr_livre INTEGER;
 BEGIN
	SELECT COUNT(*) INTO nbr_livre from emprunt e WHERE e.id_livre = $1;
	RETURN nbr_livre;
 END;
 $$
 LANGUAGE  plpgsql;


 CREATE FUNCTION age_tranche ()
 RETURNS TABLE (
 tranche_age varchar
 ) AS $$
 BEGIN
	  RETURN QUERY SELECT trunc(date_part( 'year', age(date_naissance) ) / 10) || '0-' ||
 trunc(date_part( 'year', age(date_naissance) ) / 10)+1 || '0 ans' as tranche,
 count(id)
 from abonne
 group by tranche
 order by tranche ;
 END;
 $$
 LANGUAGE  plpgsql;



 CREATE FUNCTION liste_top5 ()
 RETURNS TABLE (
 tranche_age varchar,
 id_livre int,
 nb_emprunt int
 ) AS $$
 BEGIN
	RETURN QUERY SELECT age_tranche() ,emprunt.id_livre, livre_emprunt_tranche(emprunt.id_livre) as nb  from emprunt join livre on livre.id = emprunt.id_livre group by emprunt.id_livre ORDER by nb DESC LIMIT 5;
 END;
 $$
 LANGUAGE  plpgsql;