bdd.sql

/* Supprimer une fonction */
DROP FUNCTION nombre_cartons_jaunes (character varying, character varying);

/*a AJOUTER UNE PERSONNE */
CREATE OR REPLACE function ajout_personne (nom varchar, prenom varchar, date_naissance date, nat int) returns void as $$

declare
vid int = (select max(id_personne) from personne);

begin
insert into personne values (vid +1, nom, prenom, date_naissance, nat);
end;
$$language plpgsql;

/* Pour compter un élément dans une table */
create or replace function nombre_cartons_jaunes(pnom varchar, pprenom varchar) returns void as $$
declare

nbcartons int = (select count(id_carton) from avertir as a inner join personne as p on a.id_personne = p.id_personne where a.id_personne = (select id_personne from personne where personne.nom like pnom and personne.prenom like pprenom));

begin
if (nbcartons < 5) then
    RAISE NOTICE 'Ce joueur est sympa il a recu % cartons jaunes', nbcartons;
else
    RAISE NOTICE 'Ce joueur est violent, il a recu % caronts jaune', nbcartons;
end if;

end;
$$ language plpgsql;

/* Suivre les mouvements sur une table */
create table historique(id int, quand time, commentaire varchar(250));

create or replace function maj_personne() returns trigger as $$
BEGIN

IF (TG_OP = 'INSERT') THEN
INSERT INTO HISTORIQUE VALUES 
(new.id_personne, current_timestamp, 'Opération INSERTION OK avec le nouvel identifiant'||new.id_personne||' bien inséré');
return new;

ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO HISTORIQUE VALUES (new.id_personne, current_timestamp, 'OPERATION UPDATE OK ANCIENNE VALEUR'||old.id_nationalite||' NOUVELLE VALEUR'||new.id_nationalite||'.');
return new;

ELSIF (TG_OP = 'DELETE') THEN
INSERT INTO HISTORIQUE VALUES (old.id_personne, current_timestamp, 'OPERATION DELETE OK '||old.id_personne|| 'DELETED');
return OLD;

END IF;
END;
$$ LANGUAGE plpgsql;

/* Le trigger */
create trigger maj_personne
AFTER insert OR UPDATE OR DELETE ON personne
for each row
execute procedure maj_personne();

/* LES DEUX METHODES DE BOUCLE */

create or replace function joueurs_1999() returns void as $$

declare
curseur cursor for select nom,prenom from personne where date_naissance between '1999-01-01'
and '1999-12-21' order by nom;
vnom personne.nom%type;
vprenom personne.prenom%type;

begin
open curseur;
loop
FETCH curseur INTO vnom,vprenom;
exit when not found;
raise notice 'ce joueur dont le nom est % et le prénom est % est né en 1999.',vnom,vprenom;
end loop;
close curseur;
end;
$$ language 'plpgsql';


/* deuxième méthode FOR LOOP*/

create or replace function joueurs_1999() returns void as $$
declare

mavariable_ligne RECORD;
begin

FOR mavariable_ligne IN SELECT nom, prenom from personne where date_naissance between '1999-01-01'
and '1999-12-21' order by nom

loop
raise notice 'ce joueur dont le nom est % et le prénom est % est né en 1999.',mavariable_ligne.nom, mavariable_ligne.prenom;
end loop;

end;
$$ language 'plpgsql';