Skip site navigation (1) Skip section navigation (2)

Bug #501: plpgsql, date data type and time change

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #501: plpgsql, date data type and time change
Date: 2001-10-30 02:20:40
Message-ID: 200110300220.f9U2Kej80342@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Eric Prevost-Dansereau (eric(at)esc(dot)rosemere(dot)qc(dot)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 charge a 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 student can 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 dclanche une erreur
   IF taux IS NULL THEN
      RAISE EXCEPTION ''Un taux d''''amende doit tre spcifi 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;

   --Dterminer le nombre de jours ferms
   --Jours de la semaine ferms
   SELECT count(dow)*7 INTO nb_joursFermes FROM joursSemFermes;
   nbJours:=nbJours-nb_joursFermes;
   --Dates fermes
   SELECT count(ferme) INTO nb_joursFermes from joursfermes WHERE ferme BETWEEN dateRetour and dateRemise;
   nbJours:=nbJours-nb_joursFermes;

   --Vrifier 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;
      --Incrmenter 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


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-10-30 05:29:32
Subject: Re: Bug #501: plpgsql, date data type and time change
Previous:From: Tatsuo IshiiDate: 2001-10-30 01:02:09
Subject: Re: postgresql-7.1.3 bugs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group