CREATE OR REPLACE FUNCTION file_intervention (id_marche integer) RETURNS text AS $$

DECLARE

	resultat text;

BEGIN

	CREATE TEMP TABLE intervention_aramis AS
	select
	i.id_unite_maintenance,
	i.id_interventions, 
	i.cd_intervention, 
	i.date_creation, 
	i.date_enregistrement, 
	i.type_intervention, 
	i.etat, 
	i.date_debut_intervention, 
	i.date_fin_intervention,
	s.id_responsable,
	s.nom_societe,
	inter.id_personnes,
	0 as nature


	from interventions i
	inner join prestations p 
	on p.id_prestations = i.id_prestations
	inner join intervenants inter
	on inter.id_interventions = i.id_interventions
	inner join contrats c
	on c.id_contrats = p.id_contrats and c.id_contrats=id_marche
	inner join societes s
	on s.id_societes = c.id_societes

	and i.type_intervention=105

	union

	select
	i.id_unite_maintenance,
	i.id_interventions, 
	i.cd_intervention, 
	i.date_creation, 
	i.date_enregistrement, 
	i.type_intervention, 
	i.etat, 
	i.date_debut_intervention, 
	i.date_fin_intervention,
	s.id_responsable, 
	s.nom_societe,
	inter.id_personnes,
	dn.id_natures as nature

	from interventions i
	inner join intervenants inter
	on inter.id_interventions = i.id_interventions
	inner join demandes d 
	on d.id_demandes = i.id_demandes
	inner join contrats c
	on d.id_contrats = c.id_contrats and c.id_contrats=id_marche
	inner join societes s 
	on s.id_societes = d.id_societes
	inner join demandes_natures dn
	on dn.id_demandes = d.id_demandes

	and i.type_intervention=104;

	COPY intervention_aramis
	TO 'g:/ARAMIS/intervention.txt' DELIMITER ';' ;

DROP  TABLE intervention_aramis;
RETURN resultat;

END;
$$ LANGUAGE plpgsql;

--SELECT * FROM file_intervention (5)