1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53
| REATE OR REPLACE FUNCTION "public"."analyse_union_1" ("Debut" varchar, "Fin" varchar) RETURNS SETOF record AS
$body$
DECLARE
rec RECORD;
BEGIN
TRUNCATE TABLE "Stat_AnalyseUnion1";
-- REQUETE ANALYSE FILTRE 1
CREATE TEMPORARY TABLE "tempfiltre1" AS
SELECT
(SUBSTRING (sunocfe FROM 1 FOR 1) || sutyplia) AS nocfe,
(SUBSTRING(sudtcreat FROM 1 FOR 4) || ' ' || SUBSTRING (sudtcreat FROM 5 FOR 2)) AS temp_mois,
sulieven,
COALESCE(codebassin, 'AUTRE') as codebassin
FROM
"vuesuivi"
LEFT JOIN geobassin
ON (sucodpos || sucomm) = commune
WHERE
sulieven like '0%'
AND ((sudtcreat >= $1)
and (sudtcreat <= $2)) ;
-- REQUETE ANALYSE 1
FOR rec IN
( SELECT
numero,
libelle,
typcfe,
mois,
design_bassin,
count(sulieven) as nombre
FROM
"Stat_ReqAnaPERIODE"
LEFT JOIN tempfiltre1
ON code_bassin = codebassin
AND code_evt = nocfe
AND mois = temp_mois
GROUP BY
numero, libelle, typcfe,mois,design_bassin
HAVING
(numero = 1)
ORDER BY
typcfe, mois, design_bassin )
LOOP
RETURN NEXT rec ;
END LOOP ;
RETURN ;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; |
Partager