Forums PostgreSQL.fr

Morby

Membre

probleme de noob : requete et triggers

toujours sur mon projet scolaire "réalisez la BD pour un mini-wiki" j'approche du bout mais je bloque.... voici mes tables

table des utilisateurs

DROP TABLE LGPUTILS CASCADE;
CREATE TABLE LGPUTILS
       (NUMUSER SERIAL,
        NOM VARCHAR(25) PRIMARY KEY,
    PASSWORD VARCHAR(25) NOT NULL,
    NBARTICLECREE INTEGER DEFAULT '0'
    );

-- droits d'accès pour la séquence (SERIAL)
GRANT USAGE on lgputils_numuser_seq to public;

--création d'une vue qui permet de voir tous les attributs de la table
DROP VIEW LGPUSERNEW;
CREATE VIEW LGPUSERNEW AS SELECT * FROM LGPUTILS;

grant select on LGPUSERNEW to public;
grant insert on LGPUSERNEW to public;
grant update on LGPUSERNEW to public;

-- création d'une règle sur la vue "LGPUSERNEW"
-- permettant l'insertion de nouveaux utilisateurs avec automatisme : nom = compte utilisateur
DROP RULE usernew_insert on LGPUSERNEW CASCADE;
CREATE RULE usernew_insert AS ON INSERT TO LGPUSERNEW DO INSTEAD
    INSERT INTO LGPUTILS VALUES
    (default,
    current_user,
    NEW.PASSWORD);

table des sujets publiés :

DROP TABLE LGPSUJET CASCADE;
CREATE TABLE LGPSUJET
       (NUMSUJET SERIAL PRIMARY KEY,
    AUTEUR VARCHAR(25) REFERENCES LGPUTILS (NOM),
    TITRE VARCHAR NOT NULL,
    DATEPARUTION DATE DEFAULT current_date,
    DATEMODIF DATE DEFAULT current_date,
    AUTEURMODIF VARCHAR(25) REFERENCES LGPUTILS (NOM),
    NUMBACKUP INTEGER DEFAULT '1',
    NBVUE INTEGER DEFAULT '0',
    TEXTE VARCHAR NOT NULL,
    PROTECTION INTEGER DEFAULT '1'    );

-- droits d'accès pour la séquence (SERIAL)
GRANT USAGE on lgpsujet_numsujet_seq to public;

-- création d'une vue qui permet de voir tous les sujets de la table
CREATE VIEW ALLSUJET AS SELECT * FROM LGPSUJET;
grant select on ALLSUJET to public;
grant insert on ALLSUJET to public;

-- création d'une règle qui permet l'insertion de nouveaux sujets à travers la vue ALLSUJET
DROP RULE sujet_insert ON ALLSUJET;
CREATE RULE sujet_insert AS ON INSERT TO ALLSUJET DO INSTEAD
    INSERT INTO LGPSUJET (auteur, titre, texte) VALUES
    (current_user,
    NEW.TITRE,
    NEW.TEXTE);

-- règle pour update du compteur "nbre d'article créés"
DROP RULE cpt_update on LGPUSER CASCADE;
CREATE RULE cpt_update AS ON UPDATE TO LGPUSERNEW
    DO INSTEAD
    UPDATE LGPUTILS
    SET nbarticlecree= NEW.nbarticlecree
    WHERE nom = old.nom;

______________________________________

Problème n°1 :
impossible de faire foncitonne ma requete ci-dessous, j'ai une erreur sur le WHERE de la ligne n°5 et je ne comprend pas pourquoi...

-- requete de mise à jour générale du compteur "nbre d'article créés"
-- en cas de suppression d'article
-- vues utilisées : LGPUSERNEW + ALLSUJET
UPDATE LGPUSERNEW SET NBARTICLECREE =
    (SELECT count(auteur), nom
    FROM ALLSUJET
    GROUP BY auteur
    WHERE auteur =
    (SELECT distinct auteur
    FROM ALLSUJET,LGPUSERNEW
    WHERE nom=auteur) ) ;

Problème n°2 :
j'ai un trigger à faire dans lequel je dois mettre la requête ci-dessous

-- requete d'incrémentation du compteur "nbre d'article créés"
-- a insérer dans un trigger de type "after" se déclenchant après nouvel INSERT dans ALLSUJET
UPDATE LGPUSERNEW
    SET NBARTICLECREE = NBARTICLECREE+1
    WHERE nom=current_user;

Le soucis est qu'on a fait 2h sur les triggers sous Oracle, mais pas du tout sous Postgres, d'après ce que j'ai vu sur les forums la syntaxe est radicalement différente (fonction + trigger), j'ai essayé de le faire mais j'arrive à rien... suite à ça j'ai 2 autres triggers plus complexes à faire, un modèle serait bienvenu pcq là, je patauge royalement et la date de remise du projet avance à grand pas

merci d'avance pour votre aide

gleu

Administrateur

Re : probleme de noob : requete et triggers

impossible de faire foncitonne ma requete ci-dessous, j'ai une erreur sur le WHERE de la ligne n°5 et je ne comprend pas pourquoi...

Je suppose (ou plus exactement devine) que le WHERE doit être avant le GROUP BY. Ce serait plus simple si vous donniez le message d'erreur, histoire qu'on n'ait pas à le deviner aussi.

j'ai essayé de le faire mais j'arrive à rien

Commencez par montrer ce que vous avez fait, et l'erreur que vous obtenez.

---

Guillaume.

Morby

Membre

Re : probleme de noob : requete et triggers

j'ai modifié ma requête en suivant vos conseils et maintenant j'ai une autre erreur:

UPDATE LGPUSERNEW SET NBARTICLECREE =
    (SELECT count(numsujet)
    FROM ALLSUJET
    WHERE auteur =
    (SELECT distinct auteur
    FROM ALLSUJET,LGPUSERNEW
    WHERE nom=auteur)
    GROUP BY auteur) ;

ERREUR:  plus d'une ligne renvoyée par une sous-requête utilisée comme une expression

autre tentative :

UPDATE LGPUSERNEW SET NBARTICLECREE =
    (SELECT count(numsujet)
    FROM ALLSUJET
    WHERE auteur =
    (SELECT distinct nom
    from LGPUSERNEW)
    GROUP BY auteur );

ERREUR:  plus d'une ligne renvoyée par une sous-requête utilisée comme une expression

je craque....

-----------------------------------

et pour le trigger, voila ce que j'ai essayé de faire :
incrémentation automatique du compteur NBARTICLECREE d'un utilisateur à chaque fois qu'il fait un nouvel insert dans la vue ALLSUJET

CREATE FUNCTION fonction_increment_crearticle() RETURNS ????
BEGIN
UPDATE LGPUSERNEW
    SET NBARTICLECREE = NBARTICLECREE+1
    WHERE nom=current_user;
END;

CREATE TRIGGER fonction_increment_crearticle AFERT INSERT ON allsujet
FOR EACH ROW EXECUTE PROCEDURE fonction_increment_crearticle();

je n'ai pas trouvé de doc suffisamment explicite (en français) qui explique si je dois mettre un returns ou pas... je suis en plein brouillard !
merci pour votre aide

Dernière modification par Morby (10/01/2012 02:24:16)

gleu

Administrateur

Re : probleme de noob : requete et triggers

je craque....

Si vous expliquiez ce que vous voulez faire...

incrémentation automatique du compteur NBARTICLECREE d'un utilisateur à chaque fois qu'il fait un nouvel insert dans la vue ALLSUJET

Dans ce cas, il vous faut un trigger. Autrement dit, vous devez créer une fonction trigger, puis un trigger. Une fonction trigger renvoie toujours le type trigger. La fonction doit renvoyer la ligne (modifiée ou non) pour que l'INSERT se fasse ou NULL pour que l'INSERT soit annulé. Comme vous faites un UPDATE dans LGPUSERNEW, il vous faut créer une règle sur la vue LGPUSERNEW pour transformer cet UPDATE en UPDATE dans la bonne table. Quant au trigger créé, il ne peut pas fonctionner sur une vue (car si je ne me trompe pas allsujet est une vue). Il faut utiliser la clause INSTEAD OF.

---

Guillaume.

flo

Membre

Re : probleme de noob : requete et triggers

autre tentative :

UPDATE LGPUSERNEW SET NBARTICLECREE =
    (SELECT count(numsujet)
    FROM ALLSUJET
    WHERE auteur =
    (SELECT distinct nom
    from LGPUSERNEW)
    GROUP BY auteur );

ERREUR:  plus d'une ligne renvoyée par une sous-requête utilisée comme une expression

Moi non plus je ne comprends pas ce que vous voulez faire.
En tout cas, ça ne peut pas fonctionner, car la sous-requête :
SELECT distinct nom
    from LGPUSERNEW

ramène la LISTE des auteurs.
Vous demandez donc, dans la clause WHERE, de comparer "auteur" avec la liste de tous les noms présents dans la table LGPUSERNEW. Or il faudrait n'avoir qu'un seul nom...

Que voulez-vous donc faire avec cette requête?

Dernière modification par flo (10/01/2012 12:09:16)

Morby

Membre

Re : probleme de noob : requete et triggers

ma requête est sensée :
- lire la vue ALLSUJET groupée par auteur
- compter le nombre de sujets pour chacun d'eux
- et updater la colonne NBARTICLECREE de la vue LGPUSERNEW pour chacun d'eux

flo

Membre

Re : probleme de noob : requete et triggers

(supprimé car faux, ça m'apprendra à faire 2 choses en même temps   )

Dernière modification par flo (10/01/2012 17:48:10)

gleu

Administrateur

Re : probleme de noob : requete et triggers

Marchera pas

Je parierais plutôt sur quelque chose comme ça :

UPDATE LGPUSERNEW SET NBARTICLECREE = tmp.count
FROM
(
  SELECT auteur, count(*)
  FROM ALLSUJET
  GROUP BY auteur
) tmp
WHERE tmp.auteur = nom;

---

Guillaume.

flo

Membre

Re : probleme de noob : requete et triggers

Certes

Morby

Membre

Re : probleme de noob : requete et triggers

Marchera pas

Je parierais plutôt sur quelque chose comme ça :

UPDATE LGPUSERNEW SET NBARTICLECREE = tmp.count
FROM
(
  SELECT auteur, count(*)
  FROM ALLSUJET
  GROUP BY auteur
) tmp
WHERE tmp.auteur = nom;

je n'avais pas pensé à cette façon de procéder
ça fonctionne impeccablement, merci

------------------------------------------

par contre concernant les trigger, je suis toujours au point mort, j'ai pas compris grand chose aux explications qui ont été données ci-dessus. un exemple de code qui fonctionne me serait vraiment d'un grand secours, sachant que je dois faire d'autres triggers, dont un bcp plus complexe

revoici mon code :

CREATE FUNCTION fonction_increment_crearticle()
BEGIN
UPDATE LGPUTILS
    SET NBARTICLECREE = NBARTICLECREE+1
    WHERE nom=current_user;
END;

CREATE TRIGGER fonction_increment_crearticle AFERT INSERT ON allsujet
FOR EACH ROW EXECUTE PROCEDURE fonction_increment_crearticle();

Morby

Membre

Re : probleme de noob : requete et triggers

personne ne veut m'aider pour mon trigger ?

Marc Cousin

Membre

Re : probleme de noob : requete et triggers

Il y a des exemples de fonctions trigger qui fonctionnent ici:  http://docs.postgresql.fr/9.1/plpgsql-trigger.html

---

Marc.

Morby

Membre

Re : probleme de noob : requete et triggers

Merci Marc, grace à ces exemples j'ai pu arranger mon code pour que la fonction et le trigger se créé dans postgres

CREATE FUNCTION fonction_increment_crearticle() RETURNS TRIGGER AS $$
BEGIN
UPDATE LGPUSERNEW 
	SET NBARTICLECREE = NBARTICLECREE+1
	WHERE nom=current_user;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER fonction_increment_crearticle AFTER INSERT ON lgpsujet
FOR EACH ROW EXECUTE PROCEDURE fonction_increment_crearticle();

Pour info LGPSUJET est la table réelle, alors que ALLSUJET est une vue sur LGPSUJET

par contre quand je fais un INSERT dans ALLSUJET, j'ai le message d'erreur suivant :
ERREUR:  le contrôle a atteint la fin de la procédure trigger sans RETURN
CONTEXTE : fonction PL/pgsql « fonction_increment_crearticle »

je n'ai toujours pas compris à quoi sert précisément RETURNS. ici j'ai pas besoin de RETURNS mais si j'en met pas la fonction ne veut pas s'enregistrer. j'ai essayé avec RETURNS NULL, mais ça veut pas non plus

Marc Cousin

Membre

Re : probleme de noob : requete et triggers

Une fonction trigger doit toujours faire un RETURN.

Si c'est un trigger after, elle doit faire :
- RETURN NULL si elle veut empêcher l'évènement qui l'a déclenché (donc empêcher la mise à jour qui a déclenché le trigger)
- RETURN NEW si elle veut maintenir l'évènement qui l'a déclenchée. En l'occurrence, ici, c'est un RETURN NEW qu'il faut à la fin de la fonction.

Et c'est RETURN sans S

---

Marc.

Morby

Membre

Re : probleme de noob : requete et triggers

avec un RETURN sans S

CREATE FUNCTION fonction_increment_crearticle() RETURN NEW AS $$
BEGIN
UPDATE LGPUSERNEW 
	SET NBARTICLECREE = NBARTICLECREE+1
	WHERE nom=current_user;
END;
$$ LANGUAGE plpgsql;

ERREUR:  erreur de syntaxe sur ou près de « RETURN »
LIGNE 1 : CREATE FUNCTION fonction_increment_crearticle() RETURN NEW A...
                                                                                         ^

------------------------
avec un RETURNS

CREATE FUNCTION fonction_increment_crearticle() RETURNS NEW AS $$
BEGIN
UPDATE LGPUSERNEW 
	SET NBARTICLECREE = NBARTICLECREE+1
	WHERE nom=current_user;
END;
$$ LANGUAGE plpgsql;

ERREUR:  le type « new » n'existe pas

autre question : dans le trigger, est-ce que la mention "for each row" est obligatoire ?
les inserts de sujets se font à l'unité, et le trigger s'effectue à l'unité aussi, je voudrais pas que le compteur soit incrémenté pour tous les utilisateurs de la table des utilisateurs

Dernière modification par Morby (12/01/2012 11:43:12)

Marc Cousin

Membre

Re : probleme de noob : requete et triggers

Ah, ok, compris ce qui vous embrouille depuis le début:
Il faut garder le RETURNS TRIGGER au début. C'est la déclaration de votre fonction.

C'est dans le code qu'il faut mettre un «RETURN».

CREATE FUNCTION fonction_increment_crearticle() RETURNS TRIGGER AS $$
BEGIN
UPDATE LGPUSERNEW 
    SET NBARTICLECREE = NBARTICLECREE+1
    WHERE nom=current_user;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

---

Marc.

Morby

Membre

Re : probleme de noob : requete et triggers

yessssssssss ça marche ^^
super, encore un grand merci pour ton aide, c'était franchement pas facile pour un noob comme moi

sujet clos

Dernière modification par Morby (12/01/2012 13:19:49)