CREATE OR REPLACE VIEW view_moy_labo AS 
 SELECT view_ecart_type_tournee.trn_esp, to_number("substring"(to_char(view_ecart_type_tournee.trn_date_prel, 'FM999999'::text), 1, 4), '9999'::text) AS mois, round(avg(view_ecart_type_tournee.stddev), 2) AS et
   FROM view_ecart_type_tournee
  GROUP BY to_number("substring"(to_char(view_ecart_type_tournee.trn_date_prel, 'FM999999'::text), 1, 4), '9999'::text), view_ecart_type_tournee.trn_esp
  ORDER BY view_ecart_type_tournee.trn_esp, to_number("substring"(to_char(view_ecart_type_tournee.trn_date_prel, 'FM999999'::text), 1, 4), '9999'::text);

-- Function: import(text)

-- DROP FUNCTION import(text);

CREATE OR REPLACE FUNCTION import(text)
  RETURNS void AS
$BODY$

DECLARE
ordre_sql varchar;

BEGIN
-- on efface les tables temporaires
delete from tbl_import;
delete from tbl_temp2_tm2;

-- on importe le fichier
SET client_encoding TO 'LATIN1';
ordre_sql := 'COPY tbl_import FROM ''' || $1 || ''' WITH CSV HEADER DELIMITER '';''';
execute ordre_sql;

-- on transforme tous les champs en données numériques et on les insère dans la 2è table temp 
INSERT INTO tbl_temp2_tm2 (tm2_date_prel, tm2_tournee, tm2_esp, tm2_usine, tm2_matricule, tm2_mg)


SELECT  
	
		to_number((
		(substring(annnee,3,2) ||
		(case CHAR_LENGTH(mois)
		when 1 then '0'||mois
		when 2 then mois
		end)
		|| decade)), '999999') as date_prel, 
		
		to_number(tournee || tour, '9999') as tournee, 
		
		(case espece
		when 'Vache ' then 1
		Else  2
		end) as esp,
		
		to_number(usine, '9999'),

		to_number(matricule, '999999'),

		to_number(replace(mg, ',', '.'),'99.9')
from tbl_import where
annnee not like 'an%'
and
decade like '01' or decade like '02' or decade like '03'
and
usine not like '9999';

-- on insère les tournées dans la table tbl_tournee_trn

INSERT INTO 
tbl_tournee_trn (trn_date_prel, trn_tournee, trn_esp, trn_usine)


SELECT DISTINCT
tm2_date_prel, tm2_tournee, tm2_esp, tm2_usine FROM tbl_temp2_tm2
WHERE NOT EXISTS 
	(SELECT *
	FROM tbl_tournee_trn
	WHERE 
	tbl_tournee_trn.trn_date_prel = tbl_temp2_tm2.tm2_date_prel AND
	tbl_tournee_trn.trn_esp = tbl_temp2_tm2.tm2_esp AND
	tbl_tournee_trn.trn_tournee = tbl_temp2_tm2.tm2_tournee AND
	tbl_tournee_trn.trn_usine = tbl_temp2_tm2.tm2_usine);

-- on insère les valeurs mg dans la tbl_valeur_mg --et mon problème est là!!!!!
INSERT INTO tbl_valeur_mg (trn_id, mg_matricule, mg_val)

SELECT 
  tbl_tournee_trn.trn_id, 
  tbl_temp2_tm2.tm2_matricule, 
  tbl_temp2_tm2.tm2_mg
FROM 
  tbl_tournee_trn, 
  tbl_temp2_tm2
WHERE 
  tbl_tournee_trn.trn_date_prel = tbl_temp2_tm2.tm2_date_prel AND
  tbl_tournee_trn.trn_esp = tbl_temp2_tm2.tm2_esp AND
  tbl_tournee_trn.trn_tournee = tbl_temp2_tm2.tm2_tournee AND
  tbl_tournee_trn.trn_usine = tbl_temp2_tm2.tm2_usine;
      
            
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION import(text) OWNER TO postgres;

-- Table: tbl_tournee_trn

-- DROP TABLE tbl_tournee_trn;

CREATE TABLE tbl_tournee_trn
(
  trn_date_prel integer NOT NULL,
  trn_esp integer NOT NULL,
  trn_tournee integer NOT NULL,
  trn_usine integer NOT NULL,
  trn_id bigserial NOT NULL,
  trn_chauffeur integer,
  CONSTRAINT "clé_primaire" PRIMARY KEY (trn_id),
  CONSTRAINT "clé_2" UNIQUE (trn_date_prel, trn_esp, trn_tournee, trn_usine)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tbl_tournee_trn OWNER TO postgres;

-- Table: tbl_valeur_mg

-- DROP TABLE tbl_valeur_mg;

CREATE TABLE tbl_valeur_mg
(
  mg_id bigserial NOT NULL,
  trn_id bigserial NOT NULL,
  mg_matricule integer NOT NULL,
  mg_val numeric(4,1) NOT NULL,
  CONSTRAINT pk PRIMARY KEY (mg_id),
  CONSTRAINT fk FOREIGN KEY (trn_id)
      REFERENCES tbl_tournee_trn (trn_id) MATCH FULL
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tbl_valeur_mg OWNER TO postgres;