return: text
lang: plpgsql
src: |
  DECLARE	
  	result TEXT;
  	_champs TEXT[];
  	_i INTEGER;
  	_table TEXT;
  	_champ TEXT;
  	_affectations TEXT;
  	_selects TEXT;
  	_tests TEXT;
  	_joins TEXT;
  	_sqlcmd TEXT;
  	_affected_rows bigint;
  BEGIN
  	
  	-- Salariés sans profil
  	UPDATE rh.p_salaries
  	SET profil_id = 0 
  	WHERE profil_id IS NULL;
  	
  	INSERT INTO rh.p_profils(oid)
  	SELECT 0
  	WHERE 0 NOT IN (SELECT oid FROM rh.p_profils) AND
  		0 IN (SELECT profil_id FROM rh.p_salaries WHERE profil_id = 0);
  	-- Initialisation des champs
  	_champs = Array[
  			't_types_contrat', 'type_contrat',
  			't_motifs_debut_contrat', 'motif_debut',
  			't_motifs_fin_contrat', 'motif_fin',
  			't_qualifications', 'qualification',
  			't_services', 'service',
  			't_specialites', 'specialite',
  			't_types_temps_travail', 'type_temps_travail',
  			't_categories_socio_professionnelle', 'categorie_socio_professionnelle',
  			't_statuts', 'statut',
  			't_codes_emploi', 'code_emploi',
  			't_societes_interim', 'societe_interim',
  			't_sections_analytiques_paie', 'section_analytique_paie',
  			't_sections_analytiques', 'section_analytique',
  			't_grilles', 'grille',
  			't_grilles_groupes', 'grille_groupe',
  			't_types_horaire', 'type_horaire',
  			't_groupes_cotisant', 'groupe_cotisant',
  			't_cadre_emploi', 'cadre_emploi',
  			't_categorie_conge', 'categorie_conge',
  			't_categorie_statutaire', 'categorie_statutaire',
  			't_commission_paritaire', 'commission_paritaire',
  			't_compte', 'compte_salarie',
  			't_filiere', 'filiere',
  			't_lettre_budgetaire', 'lettre_budgetaire',
  			't_unite_fonctionnelle', 'unite_fonctionnelle'
  			];
  	_i = 0;
  	_affectations = '';
  	_selects = '';
  	_joins = '';
  	_tests = '';
  	WHILE _i <= 48  LOOP
  		_i = _i + 1;
  		_table = _champs[_i];
  		_i = _i + 1;
  		_champ = _champs[_i];
  		IF _affectations <> '' THEN
  			_affectations = _affectations || ', ' || chr(10);
  		END IF;
  		IF _selects <> '' THEN
  			_selects = _selects || ', ' || chr(10);
  		END IF;
  		IF _tests <> '' THEN
  			_tests = _tests || ' OR ' || chr(10);
  		END IF;
  		_affectations = _affectations || _champ || '_id = new_' || _champ || '_id, ' || chr(10);
  		_affectations = _affectations || _champ || '_code = new_' || _champ || '_code, ' || chr(10);
  		_affectations = _affectations || _champ || '_texte = new_' || _champ || '_texte';
  		_selects = _selects || 'COALESCE(' || _table || '.oid,0) AS new_' || _champ || '_id, ' || chr(10);
  		_selects = _selects || 'COALESCE(' || _table || '.code,' || _table || '_0.code) AS new_' || _champ || '_code, ' || chr(10);
  		_selects = _selects || 'COALESCE(' || _table || '.texte,' || _table || '_0.texte) AS new_' || _champ || '_texte';
  		_joins = _joins || chr(10) || 'LEFT JOIN rh.' || _table || ' ' || _table || '_0 ON ' ||  _table || '_0.oid = 0';  
  		_joins = _joins || chr(10) || 'LEFT JOIN rh.' || _table || ' ON ' ||  _table || '.code_original = ' || _champ || '_code_original';  
  		_tests = _tests || _champ || '_id IS DISTINCT FROM new_' || _champ || '_id OR ' || chr(10);
  		_tests = _tests || _champ || '_code IS DISTINCT FROM new_' || _champ || '_code OR ' || chr(10);
  		_tests = _tests || _champ || '_texte IS DISTINCT FROM new_' || _champ || '_texte';
  		
  	END LOOP;
  	_sqlcmd = 'UPDATE rh.p_profils SET' || chr(10);
  	_sqlcmd = _sqlcmd || _affectations || chr(10);
  	_sqlcmd = _sqlcmd || 'FROM (' || chr(10);
  	_sqlcmd = _sqlcmd || 'SELECT' || chr(10);
  	_sqlcmd = _sqlcmd || 'p_profils.oid AS new_profil_id,' || chr(10);
  	_sqlcmd = _sqlcmd || _selects || chr(10);
  	_sqlcmd = _sqlcmd || 'FROM rh.p_profils' || chr(10);
  	_sqlcmd = _sqlcmd || _joins || chr(10);
  	_sqlcmd = _sqlcmd || ') subview' || chr(10);
  	_sqlcmd = _sqlcmd || 'WHERE p_profils.oid = new_profil_id AND' || chr(10);
  	_sqlcmd = _sqlcmd || '(' || _tests || ')';
  	EXECUTE _sqlcmd;
  	GET DIAGNOSTICS _affected_rows = ROW_COUNT;
  	
  	return (_affected_rows::text || ' ligne(s) modifiée(s)')::text;
  END;