Le portail francophone de la géomatique

CREATE TABLE "temp"
(
  id serial NOT NULL,
  etat character varying(10),
  date date,
  heure time without time zone,
  CONSTRAINT pk PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE "temp" OWNER TO dba;

INSERT INTO temp (id, etat, date, heure) VALUES (1, 'active', '2008-02-28', '08:30:00');
INSERT INTO temp (id, etat, date, heure) VALUES (2, 'active', '2008-02-28', '08:45:00');
INSERT INTO temp (id, etat, date, heure) VALUES (3, 'inactive', '2008-02-28', '10:00:00');
INSERT INTO temp (id, etat, date, heure) VALUES (4, 'active', '2008-02-28', '10:30:00');
INSERT INTO temp (id, etat, date, heure) VALUES (5, 'inactive', '2008-02-28', '10:45:00');

CREATE OR REPLACE FUNCTION duree_activite(date)
  RETURNS time without time zone AS
$BODY$
DECLARE
    date_act alias for $1;
    myrec RECORD;
    var_heure_debut_phase time;
    var_heure_courante time;
    var_duree_activite interval;
    var_etat_courant text;
        var_etat_precedent text;
        var_duree_phase interval;
BEGIN
var_duree_activite:='00:00:00';
var_duree_phase:='00:00:00';
        
    FOR myrec IN 
        SELECT id, etat, date, heure FROM "temp" where date=date_act
    LOOP
        var_etat_courant:=myrec.etat;
        var_heure_courante:=myrec.heure;
        RAISE NOTICE 'début de phase : %', var_heure_debut_phase ;
    RAISE NOTICE 'état de la phase : %', var_etat_precedent ;
    RAISE NOTICE '->heure courante (fin de phase) : %', var_heure_courante ;
    
        IF var_etat_precedent='active' 
        THEN var_duree_activite:=var_duree_activite+(var_heure_courante-var_heure_debut_phase);
        END IF;
    var_duree_phase:=var_heure_courante-var_heure_debut_phase;
    var_heure_debut_phase:=var_heure_courante;
    var_etat_precedent:=var_etat_courant;
            
    RAISE NOTICE 'durée de la dernière phase : %', var_duree_phase ;
    RAISE NOTICE 'durée de l''activité journalière : %', var_duree_activite;
    END LOOP;
    RETURN var_duree_activite;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE

select duree_activite('2008-02-28');