Discussion :

[Oishiiii]

Bonjour,

Je débute avec PostgreSQL (v8.4) et j'essaie de créer un trigger qui va m'aider à gérer l'identification relative d'une table enfant par rapport à sa table parent.

Les deux tables pour le test:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
create table t_parent (
	id serial,
	nom varchar(30),
	CONSTRAINT pk_parent PRIMARY KEY(id)
);
insert into t_parent(nom) values ('n1');
insert into t_parent(nom) values ('n2');
insert into t_parent(nom) values ('n3');
insert into t_parent(nom) values ('n4');
 
create table t_enfant (
	parent_id serial,
	enfant_id integer,
	nom varchar(40),
	CONSTRAINT pk_enfant PRIMARY KEY (parent_id, enfant_id),
	CONSTRAINT fk_enfant_parent FOREIGN KEY (parent_id) REFERENCES t_parent(id)
);

L'objetif dans la table enfant est d'obtenir ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
parent_id  enfant_id  nom
-----------------------------
     2         1      e1
     2         2      e2
     2         3      e3
     3         1      e4
     3         2      e5
     4         1      e6
     4         2      e7

La colonne enfant_id est auto-incrémentée à partir de 1 pour chaque parent_id.

Donc je doit alimenter cette colonne dans un Trigger Before Insert.

Voilà la fonction que j'ai écrite et son trigger :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE OR REPLACE FUNCTION f_indentification_relative_enfant () RETURNS trigger AS '
	DECLARE
		idEnfant integer;
	BEGIN
		SELECT INTO idEnfant MAX(enfant_id)
		FROM t_enfant
		WHERE parent_id = NEW.parent_id;
 
		IF NOT FOUND THEN
	    	idEnfant := 1;
	    ELSE
	    	idEnfant := idEnfant +1;
	    END IF;
 
	   NEW.enfant_id := idEnfant;
 
    RETURN NEW;
  END;
' LANGUAGE 'plpgsql';

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CREATE TRIGGER t_ident_rel_enfant
    BEFORE INSERT ON t_enfant FOR EACH ROW
    EXECUTE PROCEDURE f_indentification_relative_enfant();

Je n'ai pas d'erreur de syntaxe et le code me semble bon.
Mais lorsque j'écris un ordre INSERT comme ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
insert into t_enfant(parent_id, nom) values (3, 'e1');

J'obtiens l'erreur:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
********** Error **********
 
ERROR: null value in column "enfant_id" violates not-null constraint
SQL state: 23502

Voilà, si quelqu'un trouve la source d'erreur, ou veux bien m'aider avec ce trigger, ça serait sympa.

Merci,
Bonne journée.

[estofilo]

Le IF NOT FOUND n'est pas le bon test avec une requête select MAX(...) FROM... car elle a toujours une ligne de résultat.
Le plus simple est de faire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

select INTO NEW.enfant_id 1+COALESCE(MAX(...), 0) FROM ...WHERE...

qui renvoie directement le résultat cherché, et tout le reste du code n'est plus nécessaire.

[Oishiiii]

Ok,

Merci beaucoup estofilo, ça fonctionne très bien.

[Oishiiii]

Bonjour ,

J'ai encore des soucis alors je continue sur ce topic.

Comme j'ai plusieurs tables utilisant l'identification relative, j'essaie de faire une fonction "générique" avec utilisation de variables, plutôt que d'avoir une fonction par table.

Voici un trigger:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
CREATE TRIGGER trig_ident_relative_enfant
	BEFORE INSERT ON t_enfant FOR EACH ROW
	EXECUTE PROCEDURE func_ident_relative('enfant_id', 't_enfant', 'parent_id');

Et la fonction:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
CREATE OR REPLACE FUNCTION func_ident_relative()
  RETURNS trigger AS
$BODY$
	BEGIN
 
		raise notice 'tg_argv0 : %', TG_ARGV[0];
		raise notice 'tg_argv1 : %', TG_ARGV[1];
		raise notice 'tg_argv2 : %', TG_ARGV[2];
 
		EXECUTE 'SELECT 1+COALESCE(MAX(' || quote_ident(TG_ARGV[0]) || ' ), 0) 
			FROM ' || quote_ident(TG_ARGV[1]) || ' 
			WHERE ' || quote_ident(TG_ARGV[2]) || ' = NEW.' || quote_ident(TG_ARGV[2]) || ' 
			INTO NEW.' || quote_ident(TG_ARGV[0]);
 
	RETURN NEW;
	END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

Lorsque je lance ordre INSERT, le même que précédement, j'ai une erreur à cause de NEW:

[WARNING ] insert into t_enfant(parent_id, nom) values (3, 'e1')

NOTICE: tg_argv0 : enfant_id
NOTICE: tg_argv1 : t_enfant
NOTICE: tg_argv2 : parent_id

ERREUR: NEW utilisé dans une requête qui ne fait pas partie d'une règle
LINE 1: ...X(enfant_id ), 0) FROM t_enfant WHERE parent_id = NEW.parent...
^
QUERY: SELECT 1+COALESCE(MAX(enfant_id ), 0) FROM t_enfant WHERE parent_id = NEW.parent_id INTO NEW.enfant_id

CONTEXT: PL/pgSQL function "func_ident_relative" line 7 at instruction EXECUTE

La syntaxe de la requête semble bonne et sur l'internet je n'ai pas trouvé d'information pour corriger cette erreur.

Qu'est-ce qu'il faut faire ?

Merci

[estofilo]

Dans la commande EXECUTE requete INTO resultat, la requête ne doit faire référence à aucune variable plpgsql, y compris NEW.
Il faut remplacer les références à NEW.champ par leurs valeurs réelles.

[Oishiiii]

Ok,
J'ai pu mieux orienter mes recherches.
Il semblerait qu'il est impossible en PL/pgSQL d'obtenir la valeur de NEW.TG_ARGV[0] (TG_ARGV[0] contenant un nom de colonne).

Je crois que je vais faire une fonction par table.

Merci encore.