Mot-clé - plpgsql

Fil des billets - Fil des commentaires

mardi, février 7 2006

Vacances en PL/pgsql

Pour le boulot, j'avais besoin de remplir une table avec les jours fériés d'une année. On me demandait surtout de récupérer les infos d'un fichier fourni par l'utilisateur. L'idée de concevoir un décodeur de fichier au format vCalendar m'amusait moyennement. L'idée de demander à mes clients d'enregistrer les jours fériés dans un fichier dont je fournissais le format me plaisait encore moins. Très facile pour moi, très gênant pour mes clients.

Bêtement, je me suis demandé si je ne pouvais pas calculer ces dates car je n'ai besoin que des jours fériés français. J'ai donc traîné un peu sur wikipedia. Avec cette page, j'ai constaté qu'à peu près tous les jours sont fixes, sauf deux fêtes religieuses : Pâques et l'Ascension. J'ai même trouvé un lien vers une formule mathématique permettant de calculer facilement ces dates.

J'ai commencé par écrire une procédure stockée pour calculer la date du lundi de Pâques en ne lui fournissant que l'année :

create or replace function paques(annee integer) returns timestamp as $$
declare
  a integer;
  b integer;
  r date;
begin
a := (19*(annee % 19) + 24) % 30;
b := (2*(annee % 4) + 4*(annee % 7) + 6*a + 5) % 7;
select (annee::text||'-03-31')::date + (a+b-9) into r;
return r;
end;
$$ language plpgsql;

Puis, une pour l'ascension (jeudi de la semaine des 40 jours après Pâques, d'où l'utilisation de la procédure stockée précédente) :

create or replace function ascension(annee integer) returns timestamp as $$
declare
  r    date;
begin
select paques(annee)::date + 40 into r;
select r + (4 - extract(dow from r))::integer into r;
return r;
end;
$$ language plpgsql;

Enfin, j'ai ajouté une procédure qui enregistre tous jours fériés français d'une année en lui indiquant l'année, si on souhaite supprimer les jours fériés déjà saisis pour cette année et si on habite en Alsace ou en Moselle (ces deux départements disposent de deux jours fériés supplémentaires... chanceux :) ) :

create or replace function vacances(annee integer, supp_anciens boolean, alsace_moselle boolean) returns boolean as $$
declare
  f integer;
begin
-- on regarde s'il existe déjà des jours fériés dans la table pour cette année
select fid into f from feries where date_part('year', fdate)=annee;
if found then
  -- soit on les supprime, soit on quitte sans rien faire
  if supp_anciens then
    delete from feries where date_part('year', fdate)=annee;
  else
    return false;
  end if;
end if;

-- on insère les éléments nécessaires
insert into feries (flibelle, fdate) values ('Jour de l''an'::text, (annee::text||'-01-01')::date);
insert into feries (flibelle, fdate) values ('Pâques'::text, paques(annee)::date + 1);
insert into feries (flibelle, fdate) values ('Ascension'::text, ascension(annee)::date);
insert into feries (flibelle, fdate) values ('Fête du travail'::text, (annee::text||'-05-01')::date);
insert into feries (flibelle, fdate) values ('Victoire 1945'::text, (annee::text||'-05-08')::date);
insert into feries (flibelle, fdate) values ('Fête nationale'::text, (annee::text||'-07-14')::date);
insert into feries (flibelle, fdate) values ('Assomption'::text, (annee::text||'-08-15')::date);
insert into feries (flibelle, fdate) values ('La toussaint'::text, (annee::text||'-11-01')::date);
insert into feries (flibelle, fdate) values ('Armistice 1918'::text, (annee::text||'-11-11')::date);
insert into feries (flibelle, fdate) values ('Noël'::text, (annee::text||'-12-25')::date);

-- insertion des deux jours supplémentaires dans le cas de l'Alsace et la Moselle
if alsace_moselle then
  insert into feries (flibelle, fdate) values ('Vendredi saint'::text, paques(annee)::date - 2);
  insert into feries (flibelle, fdate) values ('Lendemain de Noël'::text, (annee::text||'-12-26')::date);
end if;

return true;
end;
$$ language plpgsql;

La valeur renvoyée indique si des données ont été insérées ou non.

vendredi, septembre 24 2004

Ajouter une trace lors de la suppression d'une ligne d'une table spécifique

L'idée est de créer une fonction Pl/Pgsql et de la lier à un déclencheur (trigger pour les anglophones).

Voici à quoi doit ressembler la requête de création de la fonction :

create or replace function trace() returns trigger as '
declare
  utilisateur varchar(255);
begin
select into utilisateur current_user;
raise notice \'Suppression de la ligne % par l\'\'utilisateur %\', old.identifiant_ligne, utilisateur;
return old;
end;
' langage 'plpgsql';

Et voici la requête faisant le lien entre cette fonction et un nouveau déclencheur :

create trigger trace_matable before delete on matable for each row execute procedure trace();

Lors de la prochaine suppression, vous obtiendrez ce genre de messages dans vos traces :
NOTICE: Suppression de la ligne 880100 par l'utilisateur gl