db_biljou_carto on db.silva.inrae.fr

Function: ps_calcul_relatif

Schema

safran

Owner

postgres

Descriptions

There is no description for function ps_calcul_relatif

Options

Definition

CREATE OR REPLACE FUNCTION safran.ps_calcul_relatif (
)
RETURNS void AS
$span$
DECLARE
r0 record; -- Liste des périodes de référence
r1 record; -- Liste ordonnée des grilles, scénarios, combinaisons et indicateur relatif distincts, et année avec l'id de la carte statistique et nom de la table de données statistique correspondant
r2 record; -- Liste des 9000 mailles, et des 3 indicateurs élémentaires et des 3 indicateurs statistiques
var_sql text; -- requête sql
var_counter integer DEFAULT 0; -- compteur
i integer DEFAULT 0; -- compteur
var_nom_table_rel text; -- nom de la table de données statistiques
var_nom_table_indic text; -- nom de la table de données des indicateurs
var_last_id integer; -- id de la dernière carte créée
var_current_date date; -- date actuelle
var_caind_id integer; -- id d'une carte relative
var_defrel integer;
var_durrel integer;
var_debrel integer;
var_defrel_value text;
var_debrel_value text;
var_durrel_value text;
var_tmp text;
var_table_exist boolean;
var_schema text := 'safran';
BEGIN
FOR r0 IN
SELECT *
FROM public.tr_periode_per
ORDER BY per_duree, per_annee_debut
LOOP
i := i+1;
RAISE NOTICE '   ...calcul pour période % (à %)', i, (SELECT extract(hour from (timeofday()::timestamp))::text) || ':' || (SELECT extract(minute from (timeofday()::timestamp))::text);
FOR r1 IN
SELECT ci.caind_gri_id, ci.caind_scen_id, s.scen_code, ci.caind_comb_id, i2.ind_id as indicrel_id,
i3.ind_id as indicstat_id, ci.caind_nom_table, ci.caind_annee, cs.casind_id, cs.casind_nom_table
FROM public.t_carte_statistique_indicateur_casind as cs, public.t_carte_indicateur_caind AS ci
INNER JOIN public.tr_indicateur_ind AS i1 ON ci.caind_ind_id = i1.ind_id
INNER JOIN public.tr_indicateur_ind AS i2 ON i1.ind_id = i2.ind_id_parent
INNER JOIN public.tr_indicateur_ind AS i3 ON i1.ind_id = i3.ind_id_parent
INNER JOIN public.tr_scenario_scen AS s ON ci.caind_scen_id = s.scen_id
WHERE i1.ind_type = 'indicateur'
AND i2.ind_type = 'relatif'
AND i3.ind_type = 'statistique'
AND cs.casind_ind_id = i3.ind_id
AND cs.casind_annee_debut = r0.per_annee_debut
AND cs.casind_annee_fin = r0.per_annee_fin
AND cs.casind_gri_id = ci.caind_gri_id
AND cs.casind_scen_id = ci.caind_scen_id
AND cs.casind_comb_id = ci.caind_comb_id
ORDER BY ci.caind_gri_id, ci.caind_scen_id, ci.caind_comb_id, ci.caind_annee, ci.caind_ind_id
LOOP
var_nom_table_indic := r1.caind_nom_table;
var_nom_table_rel := replace(r1.caind_nom_table, 'indic', 'indicrel');

SELECT DATE(NOW()) INTO var_current_date;

SELECT caind_id INTO var_caind_id
FROM public.t_carte_indicateur_caind
WHERE caind_casind_id = r1.casind_id
AND caind_scen_id = r1.caind_scen_id
AND caind_gri_id = r1.caind_gri_id
AND caind_comb_id = r1.caind_comb_id
AND caind_annee = r1.caind_annee
AND caind_ind_id = r1.indicrel_id;

-- Si la carte statistique existe déjà lève une erreur
IF var_caind_id IS NOT NULL THEN
RAISE EXCEPTION 'La création d''une carte relative a échoué car cette carte existe déjà (id : %). Procédure intérrompue, aucune carte n''a été créée.', var_caind_id;
END IF;

INSERT INTO public.t_carte_indicateur_caind (caind_annee, caind_nom_table, caind_est_publiable, caind_date_integration, caind_comb_id, caind_casind_id, caind_ind_id, caind_scen_id, caind_gri_id)
VALUES (r1.caind_annee, var_nom_table_rel, true, var_current_date, r1.caind_comb_id, r1.casind_id, r1.indicrel_id, r1.caind_scen_id, r1.caind_gri_id);

-- Récupère l'id de la dernière carte créée
SELECT CURRVAL(pg_get_serial_sequence('t_carte_indicateur_caind','caind_id')) INTO var_last_id;

SELECT ind_code_fr INTO var_tmp
FROM public.tr_indicateur_ind
WHERE ind_id = r1.indicrel_id;

IF var_tmp = 'DEFREL' THEN
var_defrel = var_last_id;
ELSIF var_tmp = 'DEBREL' THEN
var_debrel = var_last_id;
ELSIF var_tmp = 'DURREL' THEN
var_durrel = var_last_id;
END IF;

IF var_counter = 2 THEN  --les 3 cartes (defrel, debrel et durrel) ont é té créées, on passe à la création des données correspondantes
-- regarde si la table de données n'existe pas déjà
SELECT EXISTS (
SELECT 1
FROM   information_schema.tables
WHERE  table_schema = var_schema
AND    table_name = var_nom_table_rel
) INTO var_table_exist;

IF var_table_exist = false THEN
var_sql := 'CREATE TABLE '|| var_schema ||'.'|| var_nom_table_rel ||'
(
 id_maille integer NOT NULL,
 annee integer NOT NULL,
 code_scenario varchar(8) NOT NULL,
 defrel numeric(6, 2),
 defrel_cartid integer,
 debrel integer,
 debrel_cartid integer,
 durrel integer,
 durrel_cartid integer,
 CONSTRAINT '|| var_nom_table_rel ||'_pkey PRIMARY KEY (id_maille, annee, code_scenario, defrel_cartid),
 CONSTRAINT '|| var_nom_table_rel ||'_defrel_cartid_fkey FOREIGN KEY (defrel_cartid)
 REFERENCES t_carte_indicateur_caind (caind_id) MATCH SIMPLE,
 CONSTRAINT '|| var_nom_table_rel ||'_debrel_cartid_fkey FOREIGN KEY (debrel_cartid)
 REFERENCES t_carte_indicateur_caind (caind_id) MATCH SIMPLE,
 CONSTRAINT '|| var_nom_table_rel ||'_durrel_cartid_fkey FOREIGN KEY (durrel_cartid)
 REFERENCES t_carte_indicateur_caind (caind_id) MATCH SIMPLE
)';
EXECUTE var_sql; -- TODO rajouter les alter table pour les droits owner
var_sql := 'GRANT ALL ON TABLE '|| var_schema ||'.'|| var_nom_table_rel ||' TO biljoucarto_ecriture';
EXECUTE var_sql;
var_sql := 'GRANT SELECT ON TABLE '|| var_schema ||'.'|| var_nom_table_rel ||' TO biljoucarto_lecture';
EXECUTE var_sql;
END IF;

FOR r2 IN EXECUTE
'SELECT d.id_maille, def, deb, dur, defmoy, debmed, durmoy
FROM '||var_schema||'.'||var_nom_table_indic||' AS d
INNER JOIN '||var_schema||'.'||r1.casind_nom_table||' AS ds ON d.id_maille = ds.id_maille
WHERE d.annee = '||r1.caind_annee||'
AND d.code_scenario = '''||r1.scen_code||'''
AND ds.code_scenario = '''||r1.scen_code||'''
AND ds.annee_debut = '||r0.per_annee_debut||'
AND ds.annee_fin = '||r0.per_annee_fin||'
ORDER BY id_maille'
LOOP
IF r2.defmoy = 0 THEN
var_defrel_value := 'null';
ELSE
var_defrel_value := CAST(r2.def AS numeric) / CAST(r2.defmoy AS numeric);
END IF;

IF r2.deb IS NULL OR r2.debmed IS NULL THEN
var_debrel_value := 'null';
ELSE
var_debrel_value := r2.deb - r2.debmed;
END IF;

-- IF r2.durmoy = 0 THEN
-- var_durrel_value := 'null';
-- ELSE
-- var_durrel_value := CAST(r2.dur AS numeric) - CAST(r2.durmoy AS numeric);
-- END IF;

var_durrel_value := r2.dur - r2.durmoy;

var_sql := '
INSERT INTO '||var_schema||'.'||var_nom_table_rel||' (id_maille, annee, code_scenario, defrel, defrel_cartid, debrel, debrel_cartid, durrel, durrel_cartid)
VALUES (
'||r2.id_maille||',
'||r1.caind_annee||',
'''||r1.scen_code||''',
'||var_defrel_value||',
'||var_defrel||',
'||var_debrel_value||',
'||var_debrel||',
'||var_durrel_value||',
'||var_durrel||'
)';

EXECUTE var_sql;
END LOOP;

var_counter := 0;
ELSE
var_counter := var_counter + 1;
END IF;
END LOOP;
END LOOP;
END;
$span$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
PARALLEL UNSAFE
COST 100;

This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 07/12/2018 13:23