/*
*  Utilisée lien_dossier_nature_travaux pour liée dossier à nature_travaux
*          et recuperer son libelle
*/
UPDATE openads.dossier
    SET text_nature_travaux_calculee = (
        SELECT
            string_agg(ntc.libelle::text, '\n' order by ntc.libelle) AS text_nature_travaux
        from (
            SELECT distinct
                famille_travaux.libelle as ftl,
                CONCAT(famille_travaux.libelle, ' / ', nature_travaux.libelle) AS libelle
            FROM
                openads.lien_dossier_nature_travaux
            INNER JOIN
                openads.nature_travaux
                    ON lien_dossier_nature_travaux.nature_travaux = nature_travaux.nature_travaux
            INNER JOIN
                openads.famille_travaux
                    ON nature_travaux.famille_travaux = famille_travaux.famille_travaux
            WHERE lien_dossier_nature_travaux.dossier = dossier.dossier
            ORDER BY famille_travaux.libelle
        ) as ntc
    )
    WHERE dossier in (select distinct dossier from openads.lien_dossier_nature_travaux);