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
