Bug #501: plpgsql, date data type and time change - Mailing list pgsql-bugs

Eric Prevost-Dansereau (eric@esc.rosemere.qc.ca) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
plpgsql, date data type and time change

Long Description
This weekend (october 28th, 2001), we went back to standard time.

When I call my function named 'amende' (the code is below), with dates before and after october 28th (ex:
amende('2001-10-27','2001-10-28'),I get the following message: 

ERROR: Memory exhausted in AllocSetAlloc(84)

But if I call amende('2001-07-01','2001-07-02') or amende('2001-07-01','2005-12-31'), no problem.

The problem seems to be in the WHILE loop, but I can't figure what's wrong.

This function is used to calculate the fine owed between two dates in a school library system.

Table joursSemFermes lists closed day of week, and yable joursermes lists dates when the library is closed. We don't
chargea fine when the library is closed. 
Table parametres has only one record. This record holds system wide settings, like fine rate, max number of books a
studentcan have,... 


Thank you.


Sample Code
CREATE FUNCTION "amende" (date,date) RETURNS real AS 'DECLARE
   dateRetour alias for $1;
   dateRemise alias for $2;
   dateJour date;
   jourSem int2;

   nbJours int4;
   nb_semaine int4;
   nb_joursRestants int2;
   nb_joursFermes int4;
   buffer text;
   taux float4;
BEGIN
   IF dateRetour < current_date THEN
   -- Trouver le taux d''amende
   select tauxamende INTO taux from parametres;
   --Si le taux d''amende est null, on déclanche une erreur
   IF taux IS NULL THEN
      RAISE EXCEPTION ''Un taux d''''amende doit être spécifié dans la table PARAMETRES'';
   END IF;
   --Charger la date du jour dans dateRemise
   IF dateRemise = ''2000-01-01 BC''::date THEN
      SELECT date(now()) into dateRemise;
   END IF;

   --Trouver le nombre de semaine entre dateRetour et dateRemise
   select int4div(dateRemise - dateRetour,7), int4mod(dateRemise - dateRetour,7) INTO nb_semaine,nb_joursRestants;
   nbJours:= (nb_semaine * 7) + nb_joursRestants;

   --Déterminer le nombre de jours fermés
   --Jours de la semaine fermés
   SELECT count(dow)*7 INTO nb_joursFermes FROM joursSemFermes;
   nbJours:=nbJours-nb_joursFermes;
   --Dates fermées
   SELECT count(ferme) INTO nb_joursFermes from joursfermes WHERE ferme BETWEEN dateRetour and dateRemise;
   nbJours:=nbJours-nb_joursFermes;

   --Vérifier les jours restants
   dateJour:=dateRemise - nb_joursRestants +1;
   WHILE (dateJour <= dateRemise) LOOP
      SELECT date_part(''dow'',dateJour) into jourSem;
      SELECT ''texte''::text INTO buffer
      WHERE jourSem IN (select dow from joursSemFermes);
      IF FOUND THEN
          nbJours:=nbJours-1;
      END IF;
      --Incrémenter la date de 1 jour
      select date(dateJour + ''1 day''::interval) into dateJour;
   END LOOP;
   RETURN round(nbJours::float4 * taux,2)::float4;
   ELSE
       RETURN 0::float4;
   END IF;
END;
' LANGUAGE 'plpgsql';



No file was uploaded with this report