Forums PostgreSQL.fr

carole_31

Membre

Fonction concatenation

Bonjour,
Je bloque sur un problème que je me permets de vous soumettre.
J'ai une table 'users' dans laquelle j'ai un champ 'username' avec un trigger "before insert" dont le résultat concatène la valeur de 2 autre champs  'firstname' et 'lastname'.

 BEGIN
    NEW.username=NEW.firstName ||'.' ||NEW.lastName ;
    RETURN NEW;
END;

Ceci fonctionne bien. Par contre je souhaite que avant chaque insert soit testé dans la table l'existance d'un meme champ 'lastname' et 'firstname' afin qu'il n'y ait pas de doublon pour les username. Il faudrait donc que si une ligne possède déja les mêmes valeurs 'lastname' et 'firstname' que celles qui vont être insérées, le username généré soit 'firstname'.'lastname'+ un chiffre incrémenté (exemple : toto.dupont1).

J'ai essayé ce genre de code ci dessous mais cela ne fonctionne pas, pourriez vous m'aider svp ?

carole

meles

Membre

Re : Fonction concatenation

Bonjour,
  dans un premier temps, je pense que le end if; avant le else if est de trop.

Par contre, que va t-il se passer lorsque on aura deja un jean.dupont et un jean.dupont1 ? on va réinsérer un jean.dupont1 de plus.

Cordialement

carole_31

Membre

Re : Fonction concatenation

Oui en fait il faudrait que jean.dupont2 soit crée et que l'incrément soit fait automatiquement par rapport au dernier chiffre inséré.... c'est encore + galère que ce que ce que je pensais.

meles

Membre

Re : Fonction concatenation

Voila quelque chose qui fonctionne, mais qui devra être amélioré pour tenir compte notamment des minuscules/MAJUSCULES.

CREATE TABLE plop( firstname VARCHAR, lastname VARCHAR, username VARCHAR);
INSERT INTO plop VALUES ('Jean','Dupont','Jean.Dupont2'),('Jean','Dupont','Jean.Dupont1'),('Jean','Dupont','Jean.Dupont');

CREATE OR REPLACE FUNCTION plip(first VARCHAR, last VARCHAR)
RETURNS VARCHAR as
$$
DECLARE
  tmp integer default 0;
BEGIN
  SELECT trim(max(username),first||'.'||last)::int into tmp FROM plop where username like first||'.'||last||'%';
  IF tmp > 0 THEN
    tmp := tmp+1;
    RETURN first||'.'||last||tmp::text;
  ELSE
    RETURN first||'.'||last;
  END IF;
END;
$$
LANGUAGE PLPGSQL;

select plip('Jean','Durand');
select plip('Jean','Dupont');

Attention, c'est surement sub-optimal, je suis sur qu'il y a d'autres solutions plus élégantes !

Cordialement

PS : évite de coller ton code dans une image, il faut tout retaper pour faire des essais !

Dernière modification par meles (03/02/2011 16:52:31)

carole_31

Membre

Re : Fonction concatenation

tout d'abord merci pour m'aider à résoudre mon souci. Je ne sais pas si j'ai mal expliqué, ce qui est fort possible, ou si je ne comprends pas comment mettre en application ton exemple pour mon cas.
En fait la valeur username n'est pas inséré par ma requête d'insert. Le username est généré automatiquement via la fonction trigger.

CREATE TABLE plop( firstname VARCHAR, lastname VARCHAR, username VARCHAR);
INSERT INTO plop VALUES ('Jean','Dupont'),('Jean','Dupont'),('Jean','Dupont');

en fait j'insère les données via un fichier csv où le username ne figure pas. Et donc le fait de faire générer automatiquement le username via le trigger serait super pour çà.
du coup je ne suis pas arrivée à adapter ton script. Désolée d'être pénible et de t'embêter encore.

Désolée aussi pour le code dans l'image mais je n'ai pas eu le choix car le pare feu de ma boite bloquait l'envoi du code sur le forum.

Carole

Dernière modification par carole_31 (03/02/2011 17:33:56)

meles

Membre

Re : Fonction concatenation

tout d'abord merci pour m'aider à résoudre mon souci. Je ne sais pas si j'ai mal expliqué, ce qui est fort possible, ou si je ne comprends pas comment mettre en application ton exemple pour mon cas.
En fait la valeur username n'est pas inséré par ma requête d'insert. Le username est généré automatiquement via la fonction trigger.

CREATE TABLE plop( firstname VARCHAR, lastname VARCHAR, username VARCHAR);
INSERT INTO plop VALUES ('Jean','Dupont'),('Jean','Dupont'),('Jean','Dupont');

en fait j'insère les données via un fichier csv où le username ne figure pas. Et donc le fait de faire générer automatiquement le username via le trigger serait super pour çà.

Ok, mais tu vas bien le stocker quelque part ce username généré pour chaque ligne ? Parce que sinon, je ne vois pas trop à quoi il va servir

Cordialement

Dernière modification par meles (03/02/2011 18:16:44)

carole_31

Membre

Re : Fonction concatenation

Oui le username est stocké dans la table plop.
Il permet aux utilisateurs de s'authentifier sur l'appli web en l'occurence Nuxeo.

Carole

gleu

Administrateur

Re : Fonction concatenation

L'adaptation est assez simple. Je n'ai pas testé le code de meles, mais après une lecture rapide, il me semble bon. Ça devrait donner ceci :

DECLARE
  tmp integer default 0;
BEGIN
  SELECT trim(max(username),firstName||'.'||lastName)::int into tmp FROM plop where username like NEW.firstName||'.'||NEW.lastName||'%';
  IF tmp > 0 THEN
    tmp := tmp+1;
    NEW.username := NEW.firstName||'.'||NEW.lastName||tmp::text;
  ELSE
    NEW.username = NEW.firstName||'.'||NEW.lastName;
  END IF;
END;

---

Guillaume.

carole_31

Membre

Re : Fonction concatenation

Bonjour et merci à vous 2 pour votre aide. grace à vous je commence à voir le bout du tunnel.
J'ai inséré le code en rajoutant un

group by firstName, lastName

après le select car cela me renvoyait une erreur lors de l'exécution.

Lorsque je fait un

INSERT INTO users(lastName, firstName)  VALUES ('DUPON','TOTO');

Lors du premier insert, le username se génère parfaitement avec la valeur TOTO.DUPON

Par contre si je retente une autre insertion avec les memes valeurs, je n'obtiens pas TOTO.DUPON1 , TOTO.DUPON2, TOTO.DUPON3 ... mais cette erreur :
ERREUR:  syntaxe en entrée invalide pour l'entier : «  »

Carole

meles

Membre

Re : Fonction concatenation

Oui, ca vient du fait que le trim ne renvoie rien (enfin si, un '') puisque dupon.toto n'a pas de suffixe, j'ai eu le tour hier, j'ai tenté de le résoudre avec une exception, mais sans succès .

Je suis en train d'essayer autre chose, mais ça ne fonctionne pas encore comme je le souhaite.

Cordialement

meles

Membre

Re : Fonction concatenation

Voila, j'ai finalement réussi

DROP TABLE IF EXISTS plop;
CREATE TABLE plop( firstname VARCHAR, lastname VARCHAR, username VARCHAR);
DROP FUNCTION IF EXISTS plip();

CREATE OR REPLACE FUNCTION plip()
RETURNS TRIGGER AS
$$
DECLARE
  tmp VARCHAR default null;
  cont integer default 0;
BEGIN
  SELECT max(username) into tmp FROM plop where firstname = NEW.firstname and lastname = NEW.lastname;
  if tmp is null then
    NEW.username := NEW.firstname||'.'||NEW.lastname;
  elsif trim(tmp, NEW.firstname||'.'||NEW.lastname) = '' then
    cont := cont + 1;
    NEW.username := NEW.firstname||'.'||NEW.lastname||cont::text;
  else
    cont := trim(tmp, NEW.firstname||'.'||NEW.lastname)::int + 1;
    NEW.username := NEW.firstname||'.'||NEW.lastname||cont::text;

  end if;
  RETURN NEW;
END
$$
LANGUAGE PLPGSQL;
 
CREATE TRIGGER plop_insert
    BEFORE INSERT OR UPDATE ON plop
    FOR EACH ROW
    EXECUTE PROCEDURE plip();

insert into plop (firstname, lastname) values ('Jean','Dupont');
insert into plop (firstname, lastname) values ('Jean','Dupont'),('Jean','Dupont');
INSERT INTO PLOP VALUES ('JEAN','DUPONT'),('JEAN','DUPONT'),('JEAN','DUPONT'),('JEAN','DURAND');

select * from plop;

Par contre, il me semble indispensable que tu gères les minuscules/majuscules, sinon, ça risque de poser des problème, à moins que tes login soient sensible à la casse.

Je n'ai pas essayer avec un \copy mais il n'y a pas trop de raisons que ça ne marche pas.

Cordialement

PS: Mon premier trigger, je suis ému

EDIT: il y a un bug, au dela de 10, il ne s'incrémente plus, qqn a une idée ?

EDIT2: Ca vient du fait que toto.toto9 est plus grand que toto.toto10 !

Dernière modification par meles (04/02/2011 12:19:57)

carole_31

Membre

Re : Fonction concatenation

Rholalalal MERCI vraiment beaucoup. C'est extra.
Pour un premier trigger tu assures .

Avec la commande copy cela ne marchait pas puisque dans le csv il n'y a pas de colonne "username" mais du coup je le fais en 2 fois : d'abord
- copy csv to users_temp
puis insertion des données de users_temp dans users (et génération auto du username donc)

Trop contente

Carole

gleu

Administrateur

Re : Fonction concatenation

Oui, un max sur du texte n'est pas la même chose qu'un max sur un entier. À mon avis, il faut réutiliser le trim mais en s'assurant qu'il y a au moins un caractère après, mais avec une variable de type chaîne. Quelque chose du type :

SELECT trim(max(username),firstName||'.'||lastName) into stringtmp FROM plop where username like NEW.firstName||'.'||NEW.lastName||'_%' order by 1 desc limit 1;

Ça ne protège toujours pas contre un toto.totoA et toto.toto1. Mais il il est possible de transformer temporairement la variable tmp en text, puis de vérifier qu'il s'agit bien d'un nombre.

---

Guillaume.

meles

Membre

Re : Fonction concatenation

Bon,
  voila a quoi j'arrive (mais ca ne gère pas les suffixes non numériques), par contre, on n'est plus limité à 10 jean.dupont:

CREATE OR REPLACE FUNCTION plip()
RETURNS TRIGGER AS
$$
DECLARE
  tmp VARCHAR;
  cont integer;
BEGIN
  NEW.firstname := initcap(NEW.firstname);
  NEW.lastname := initcap(NEW.lastname);
  NEW.username := NEW.firstname||'.'||NEW.lastname;
  SELECT max(username) into tmp FROM plop where firstname = NEW.firstname and lastname = NEW.lastname;
  IF tmp IS NULL THEN
  -- ne rien faire
  ELSIF tmp = NEW.username THEN
    NEW.username := NEW.firstname||'.'||NEW.lastname||1;
  ELSE
    SELECT MAX(TRIM(username,(NEW.firstname||'.'||NEW.lastname))::INT)+1 INTO cont FROM plop where firstname = NEW.firstname and lastname = NEW.lastname and username != NEW.username;
    NEW.username := NEW.firstname||'.'||NEW.lastname||cont;
  END IF;
  RETURN NEW;
END
$$
LANGUAGE PLPGSQL;

Cordialement

Dernière modification par meles (04/02/2011 13:44:35)

carole_31

Membre

Re : Fonction concatenation

Re,
J'ai essayé le nouveau code amélioré mais l'exécution me génère l'erreur:
ERREUR:  l'opérateur n'est pas unique : text || integer
LINE 1: SELECT NEW.firstName||'.'||NEW.lastName || 1
                                                    ^
HINT:  N'a pas pu choisir un meilleur candidat pour l'opérateur. Vous devez ajouter une
conversion explicite de type.

meles

Membre

Re : Fonction concatenation

Quelle version de postgres ?

Essayes de remplacer la ligne dans le code avec ça : SELECT NEW.firstName||'.'||NEW.lastName || '1'
Il semble ne pas vouloir faire la conversion implicitement.

Remplace également celle ci :
NEW.username := NEW.firstname||'.'||NEW.lastname||cont;

par
NEW.username := NEW.firstname||'.'||NEW.lastname||cont::TEXT;

Cordialement

carole_31

Membre

Re : Fonction concatenation

en local je teste sur la version p de postgres.
sur le serveur c'est la version 8.4 qui est installée.

J'ai testé le nouveau code et c'est tout simplement parfait. Merci beaucoup de tout coeur.

Carole

carole_31

Membre

Re : Fonction concatenation

Me revoilou avec encore une petite question.
Comme vous me l'aviez conseillé, il faudrait que le username généré soit tout le temps un minuscule, peut importe si le lastname et le firstname est en majuscule ou pas.

j'ai essayé ceci avec la fonction lower():

CREATE OR REPLACE FUNCTION plip()
RETURNS TRIGGER AS
$$
DECLARE
  tmp VARCHAR;
  cont integer;
BEGIN
  NEW.firstname := initcap(NEW.firstname);
  NEW.lastname := initcap(NEW.lastname);
  NEW.lower(username) := NEW.firstname||'.'||NEW.lastname;
  SELECT max(username) into tmp FROM plop where firstname = NEW.firstname and lastname = NEW.lastname;
  IF tmp IS NULL THEN
  -- ne rien faire
  ELSIF tmp = NEW.username THEN
    NEW.lower(username) := NEW.firstname||'.'||NEW.lastname||1;
  ELSE
    SELECT MAX(TRIM(username,(NEW.firstname||'.'||NEW.lastname))::INT)+1 INTO cont FROM plop where firstname = NEW.firstname and lastname = NEW.lastname and username != NEW.username;
    NEW.lower(username) := NEW.firstname||'.'||NEW.lastname||cont;
  END IF;
  RETURN NEW;
END
$$
LANGUAGE PLPGSQL;

entre autres choses mais cela ne fonctionne pas. Où dois je insérer la fonction svp?

Carole

meles

Membre

Re : Fonction concatenation

Bonjour,
  je n'ai pas testé, mais j'aurais mis ceci :NEW.username := lower(NEW.firstname||'.'||NEW.lastname);
à la place de ça :NEW.lower(username) := NEW.firstname||'.'||NEW.lastname;
et ainsi de suite.

Ce serait bien de modifier aussi le nom de la fonction .

Cordialement

Dernière modification par meles (15/02/2011 14:25:52)

carole_31

Membre

Re : Fonction concatenation

Re,
J'ai fait la modif comme ceci

CREATE OR REPLACE FUNCTION lower_username()
  RETURNS trigger AS
$BODY$DECLARE
  tmp VARCHAR;
  cont integer;
BEGIN
  NEW."firstName" := initcap(NEW."firstName");
  NEW."lastName" := initcap(NEW."lastName");
  NEW.username := lower(NEW."firstName"||'.'||NEW."lastName");
  SELECT max(username) into tmp FROM users where "firstName" = NEW."firstName" and "lastName" = NEW."lastName";
  IF tmp IS NULL THEN
  -- ne rien faire
  ELSIF tmp = NEW.username THEN
    NEW.username :=lower(NEW."firstName"||'.'||NEW."lastName"||'1');
  ELSE
    SELECT MAX(TRIM(username,(NEW."firstName"||'.'||NEW."lastName"))::INT)+1 INTO cont FROM users where "firstName" = NEW."firstName" and "lastName" = NEW."lastName" and username != NEW.username;
    NEW.username := NEW."firstName"||'.'||NEW."lastName"||cont::TEXT;
  END IF;
  RETURN NEW;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION concat_username() OWNER TO nuxeodm;

mais cela me génère cette erreur à l'execution du trigger insert

[WARNING  ] INSERT INTO users(
                        corps, section, article, n_emploi, type_candidature, civilite, "lastName", nom_matrimonial, "firstName", n_candidat, email)
            SELECT * FROM users_test
            WHERE not exists (
              SELECT n_candidat FROM users where users.n_candidat=users_test.n_candidat
            )
            ERREUR:  syntaxe en entrée invalide pour l'entier : « zap.zop1 »
            CONTEXT:  instruction SQL « SELECT MAX(TRIM(username,(NEW."firstName"||'.'||NEW."lastName"))::INT)+1           FROM users where "firstName" = NEW."firstName" and "lastName" = NEW."lastName" and username != NEW.username »
            PL/pgSQL function "lower_username" line 14 at instruction SQL

meles

Membre

Re : Fonction concatenation

As tu pensé à modifier le nom de la fonction dans le déclaration du trigger ? (je dirais que oui)

En fait, c'est le lower qui plombe tout, puisque on se sert de firstname et lastname pour les retrancher de username, il faudrait déclarer comme ça (code non testé) pour que les 3 soit en minuscule (mais tu ne garde pas la casse d'origine de firstname, lastname) :

CREATE OR REPLACE FUNCTION lower_username()
RETURNS TRIGGER AS
$$
DECLARE
  tmp VARCHAR;
  cont integer;
BEGIN
  NEW.firstname := lower(NEW.firstname);
  NEW.lastname := lower(NEW.lastname);
  NEW.username := NEW.firstname||'.'||NEW.lastname;
  SELECT max(username) into tmp FROM plop where firstname = NEW.firstname and lastname = NEW.lastname;
  IF tmp IS NULL THEN
  -- ne rien faire
  ELSIF tmp = NEW.username THEN
    NEW.username := NEW.firstname||'.'||NEW.lastname||1;
  ELSE
    SELECT MAX(TRIM(username,(NEW.firstname||'.'||NEW.lastname))::INT)+1 INTO cont FROM plop where firstname = NEW.firstname and lastname = NEW.lastname and username != NEW.username;
    NEW.username := NEW.firstname||'.'||NEW.lastname||cont;
  END IF;
  RETURN NEW;
END
$$
LANGUAGE PLPGSQL;

Ou, on garde comme c'était (en fait on ne touche pas à la casse de firstname et lastname) :

CREATE OR REPLACE FUNCTION lower_username()
  RETURNS trigger AS
$BODY$DECLARE
  tmp VARCHAR;
  cont integer;
BEGIN
  NEW.username := lower(NEW."firstName"||'.'||NEW."lastName");
  SELECT max(username) into tmp FROM users where "firstName" = NEW."firstName" and "lastName" = NEW."lastName";
  IF tmp IS NULL THEN
  -- ne rien faire
  ELSIF tmp = NEW.username THEN
    NEW.username :=lower(NEW."firstName"||'.'||NEW."lastName"||'1');
  ELSE
    SELECT MAX(TRIM(username,(lower(NEW."firstName"||'.'||NEW."lastName")))::INT)+1 INTO cont FROM users where "firstName" = NEW."firstName" and "lastName" = NEW."lastName" and username != NEW.username;
    NEW.username := lower(NEW."firstName"||'.'||NEW."lastName"||cont::TEXT);
  END IF;
  RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql

PS : je ne suis pas sur que d'utiliser du "camelCase" et des "doubles quotes" pour le nom des colonnes améliore la lisibilité

carole_31

Membre

Re : Fonction concatenation

Impeccable, que dire de + sinon merci

J'ai pris la première solution.

Carole